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'm very new to Power BI and struggling with a filter; I've a matrix table where I'm showing a set of sites and the % completion of activity over the last 5 weeks (the weeks is a dynamic filter set to last 5 weeks, and the % complete is a calculated field based off a count).
I'd want to filter down to 0% for my current week (but still have it show the % complete for the last 4 weeks reagardless of result). However, when I filter to less that 1 on completion %, the filter applies to all weeks in view. Any guidance on how I can overcome this so the report stays dynamic week on week?
Example:
Site | 2 | 3 | 4 | 5 | 6 |
Site A | 100% | 0% | 100% | 95% | 4% |
Site B | 100% | 100% | 0% | 100% | 0% |
Site C | 100% | 100% | 100% | 0% | 100% |
Site D | 0% | 0% | 100% | 100% | 50% |
Using the example above the filter I'd like o apply would just show me site B and not any value where is less 100%.
Thank you
Solved! Go to Solution.
Hi, @Jayne_B
According to your description and example, I think you want to just apply a filter on the percentage of Sites that are only in the current week(in my case is Week 6), right? You can try my steps:
Slicer = GENERATESERIES(0,1.05,0.05)
Percent1 =
var _currentweek=MAXX(ALL('Table'),[Week])
return
IF(
MAX([Week])<>_currentweek,MAX([Percent]),
IF(MAX([Percent])>=MIN('Slicer'[Value])&&
MAX([Percent])<MAX('Slicer'[Value]),MAX([Percent]),
BLANK()))
And I guess this can be what you want:
You can download my test pbix file here
If this result is not what you want, you can post some sample data(without sensitive data) and your expected result.
How to Get Your Question Answered Quickly
Best Regards,
Community Support Team _Robert Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi, @Jayne_B
According to your description and example, I think you want to just apply a filter on the percentage of Sites that are only in the current week(in my case is Week 6), right? You can try my steps:
Slicer = GENERATESERIES(0,1.05,0.05)
Percent1 =
var _currentweek=MAXX(ALL('Table'),[Week])
return
IF(
MAX([Week])<>_currentweek,MAX([Percent]),
IF(MAX([Percent])>=MIN('Slicer'[Value])&&
MAX([Percent])<MAX('Slicer'[Value]),MAX([Percent]),
BLANK()))
And I guess this can be what you want:
You can download my test pbix file here
If this result is not what you want, you can post some sample data(without sensitive data) and your expected result.
How to Get Your Question Answered Quickly
Best Regards,
Community Support Team _Robert Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Thank you for the detail, that's been really useful, I've been able to replicate what you've outlined and it's working.
It is possible to take this one step further? The filter to less than 100% is great, I'd like the table to show me the sites which only then fall into that filter.
Is that possible to do ?
Hi, @Jayne_B
According to your description, I guess that you want to make the Matrix only display the sites that percentage is not blank in the current week, right?
After the test, I think it’s impossible if I’m trying to use a measure or calculated column to achieve this because the measure can’t be placed as the Axis or Legend of a Matrix and the calculated column can’t be affected by Slicer in Power BI. As a result, I suggest you to use the visual filter to achieve the expected result manually. You can filter the sites with no blank value manually like this:
And I guess this can be what you want.
You can download my test pbix file here
If this result is not what you want, you can post some sample data(without sensitive data) and your expected result.
How to Get Your Question Answered Quickly
Best Regards,
Community Support Team _Robert Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
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 |