Anyone who has created a report for another user that includes page or visual level filters has run into the snag of the filter being adjusted and breaking everything. Ok, that might be a bit dramatic, but Power BI and DAX are similar to other languages like Python in that explicit measures and codes are always the first best option. When I am troubleshooting a report, it helps to see exactly what a graph or measure is supposed to be displaying. Page level filters work fine for research, but in a display, nothing beats the explicit method.
Enter the DAX function CALCULATE(). As with all functions in Power BI, there is a brief explanation of the function when typing in out, but it can be confusing. The magic of this function is that it lets the user combine what would take multiple measures into one function. The method Use the most is using the CALCULATE function to sum a column with filtered results.
I will use an extremely important KPI in reliability called equipment availability. Without going into too much detail, this metric takes into account all maintenance related downtime, both planned and unplanned. The report creator has 2 simple options; dealers choice, depending on the intent of the report.
Option 1: Page/visual level filters
This method is used typically for research when the filter changes frequently. The ease of changing presents its own set of problems; when a user is unfamiliar with Power BI, clicking can snarl a report quickly.
Option 2: use the CALCULATE() and FILTER functions
Maintenance Downtime = CALCULATE ( [AES Duration (hrs)] FILTER ( 'Time Category', 'Time Category'[Key] = 7 ) ) + CALCULATE ( [AES Duration (hrs)]/COUNT('Equipment Info'[asset id]), FILTER ( 'Time Category', 'Time Category'[Key] = 8 ) )
In effect, the same end result is achieved, but the measure is static rather than dynamic, as long as the code to the measure remains the same. Using the CALCULATE() function removes opportunity for the end user to inadvertently click where they weren't supposed to be clicking, saving headaches for the report creator.
If this explanation helped, feel free to comment or share, and check out Microsoft's FAQ for a more in depth explanation.
Comentários