Oil analysis is a critical tool in machine reliability. It allows the analyst to diagnose current health and rough location on a potential-for-failure (P-F) curve. Many companies offer consulting services that range from interpretation to full service collection and reporting. The benefits of an oil analysis program are established and wide-ranging.
For more lean operations, outsourcing this important analysis task can become cost prohibitive. In these cases, collection and analysis can competently be performed with little up front training as long as the data user interface is built with the end user in mind, but workarounds can be performed on the back end if that isn't the case.
It was latter this scenario I found myself in trying to optimize the oil analysis program at a new job. Long term trending was not available on the hosted website, so I decided to remedy that situation. What follows will be a how-to on switch tables in Microsoft Power BI using Oil Analysis as the catalyst. I needed a user friendly way to rapidly switch among different particles analyzed, but the data was organized such that each different particle was a different column in the SQL dataset. A sample is shown below.
Constraints in Power BI, by design, making switching a column value on a graph cumbersome. After a few hours of trying different scenarios and searching message boards. I found a solution that has proved to be both user friendly and robust. It involved creating a dummy table with an index column and each contaminant being a row value.
Once this table was created, I wrote a DAX switch function that allowed the user to perform one click selections of the various options.
Measure = SWITCH ( SELECTEDVALUE ( 'Particle Switch function'[Particle] ), "Barium", SUM ( 'Oil data'[Barium] ), "Boron", SUM ( 'Oil data'[Boron] ), "Chromium", SUM ( 'Oil data'[Chromium] ), "Calcium", SUM ( 'Oil data'[Calcium] ), "Copper", SUM ( 'Oil data'[Copper] ), "Glycol", SUM ( 'Oil data'[Glycol] ), "Iron", SUM ( 'Oil data'[Iron] ), "Lead", SUM ( 'Oil data'[Lead] ),...)
Pictured left is the slicer mentioned above. Because of the DAX "switch" function used, no relationship between tables was needed.
This report has proved to be rewarding from lessons learned during creation and from an analysis perspective. It mimics what a 3rd party would charge a large subscription to do and has given me a much better understanding of asset health.
If this has sparked an idea or potential use case, please leave a comment.
コメント