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.
Hi,
I have a fun challenge I've been working on for the last week or so and it's got me stumped.
Here's my data set:
Project # | Job # | Date | Count | Category |
1 | 1 | 1/1/2022 | 5 | Red |
1 | 2 | 2/1/2022 | 10 | Green |
2 | 1 | 3/1/2022 | 15 | Blue |
What I need to do is for the Count to be returned for only the job who has the Max date on each project. So Project 1 would only return a Count of 10 since Job #2 is the Max date for that project. It also needs to be dynamic. So if Job #2 was filtered out, Project 1 would return a Count of 5 (since Job #1 now has the Max date for the Project in the current filter context).
A colleuge was able to help me solve this issue. I created a measure that returned on the Max date for each Project based on the current filter context.
And if I filter down to only the Job #1s, it's still all roses as the Project Max Date updates to reflect the new Max date for Project one of 1/1/22.
However, there is a twist to this story, hence why I'm posting.
I created a measure that would only return the Count for the Jobs with the Max date for each project. So based off this table being unfiltered, it should return a count of 25. And it does, as you can see in the visual.
And if I filter down to just Project 1, it gives the correct value of 10...
However, here's the rub. When I try to add an additional filter into the bar chart, it returns both categories, instead of just the one with the max date for the project. I want it to return just Green with a Count of 10, since that's the job that has the Max date for the project.
I've been playing around with different combinations of Filter and the ALL functions but I am stumped. Any help is much appreciated!
Thanks for the repsonse. I believe the solution is good but let me build it into my dashboard tomorrow and make sure there's nothing that goes wonky before we close this post.
Hi @Anonymous
I just wanted to confirm if you resolved this issue? If yes, you can accept the answer helpful as the solution or share you method and accept it as solution, thanks for your contribution to improve Power BI.
If you need more help, please let me know.
Best Regards,
Community Support Team _Tang
If this post helps, please consider Accept it as the solution to help the other members find it more quickly.
using SELECTEDVALUE inside a CALCULATE is asking for trouble. Move that outside into a variable, or use ALLEXCEPT.
Project Max Date (working) = CALCULATE(max(Sheet1[Date]),ALLEXCEPT(Sheet1,Sheet1[Project #]))
Then you need to consider the row context of your virtual table to arrive at the correct formula for the totals.
Count_ = sumx(filter(Sheet1,[Max Date?]),Sheet1[Count])
With this in place your category filters will work too. See pbix attached.
This is close. But I think there is still one missing piece. The Project Max Date (working) needs to reflect the Max date of a project based on the jobs in the current filtered selection.
When there is nothing filtered, then this measure works correctly.
However, if the dashboard is filtered down to just jobs that are #1, then it doesn't work. In this instance, it should now show Project #1s Max date as 1/1/22. It's still showing Project #1s Max date as 2/1/22 (Job #2's date) even though Job #2 has been filtered out.
Consider using REMOVEFILTERS instead of ALLEXCEPT.
Unfortunately, when I do that, it doesn't work either.
Remove the Job # filters, not the Project # filters
Hey,
Just wanted to follow up on this. If you're stuck, let me know and I can repost the issue to try and get it back to the top of the queue. Thanks!
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 |
---|---|
114 | |
105 | |
78 | |
68 | |
63 |
User | Count |
---|---|
148 | |
107 | |
106 | |
83 | |
70 |