Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hello, I search other topics and I can't seem to find a solution.
I have a simple table, is a view and all metrics are already there. With those metrics I created three aggregated measures and created three visuals that are filtered by slicers.
Everything works fine so far. One of the tables shows the availability% by machine by shift. With certain values in the slicers, the other visuals show only three machines, whereas the availability% table shows two more with availability% = 0 (this is expected due to the structure of the view)
The thing is, when I want to filter the Availability% table to show only the result aggregated value different from zero, the filter of the visual doesn't work. I pretty new to powerbi and what I imagine powerBI is doing is filtering values at row level and not the aggregated result.
This is what I wanted to achieve:
Is this the expected behavior? If so, how can I filter by the aggregated value?
Edit: I cannot attached a file, but here's the direct link to it
https://drive.google.com/file/d/151_BNSKfKGdYOyfqlWw7Z41WXuyHeVsH/view?usp=drive_link
Thanks in advance!
Solved! Go to Solution.
what I imagine powerBI is doing is filtering values at row level and not the aggregated result.
Mostly Correct. You usually need to create another filter measure that returns 0 or 1 (for example) based on aggregate calculations ( usually done via REMOVEFILTERS ) on each detail row.
In your case however the "False" branch of the IF statement returned 0. When you remove that you get the desired result.
Availability% = IF(SUM('Sheet 15_Full Data'[Available_Prod_Dur_Machine ( uptime+downtime)]) <> 0, Divide(SUM('Sheet 15_Full Data'[Prod_Dur_Machine]),SUM('Sheet 15_Full Data'[Available_Prod_Dur_Machine ( uptime+downtime)]),0))
Also note that the use of DIVIDE is preferred over "/" as it allows you to gracefully handle bad divisions.
what I imagine powerBI is doing is filtering values at row level and not the aggregated result.
Mostly Correct. You usually need to create another filter measure that returns 0 or 1 (for example) based on aggregate calculations ( usually done via REMOVEFILTERS ) on each detail row.
In your case however the "False" branch of the IF statement returned 0. When you remove that you get the desired result.
Availability% = IF(SUM('Sheet 15_Full Data'[Available_Prod_Dur_Machine ( uptime+downtime)]) <> 0, Divide(SUM('Sheet 15_Full Data'[Prod_Dur_Machine]),SUM('Sheet 15_Full Data'[Available_Prod_Dur_Machine ( uptime+downtime)]),0))
Also note that the use of DIVIDE is preferred over "/" as it allows you to gracefully handle bad divisions.
I see my error now!
Thanks a lot for your time and recommendations!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
105 | |
94 | |
75 | |
63 | |
62 |
User | Count |
---|---|
137 | |
105 | |
104 | |
80 | |
63 |