Trouble with Chart when Additional Filter Context is Added

[Anonymous]

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.

Project Max Date (working) = CALCULATE(MAX(Sheet1[Date]),

                                                                   FILTER(AllSELECTED(Sheet1), Sheet1[Project #] = SELECTEDVALUE(Sheet1[Project #])))

I then Created a Measure that simply compares the specific Jobs date with the Max Date of the Project (for the given filter context).

Max Date? = MAX(Sheet1[Date]) = [Project Max Date (working)]

Worked like a charm. Here is how the matrix looks.

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!

[Anonymous]

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. 

[v-xiaotang]

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.

[lbendlin]

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.

[Anonymous]

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.

[lbendlin]

Consider using REMOVEFILTERS instead of ALLEXCEPT.

[Anonymous]

Unfortunately, when I do that, it doesn't work either.

Project Max Date (REMOVE FILTERS) = CALCULATE(max(Sheet1[Date]), REMOVEFILTERS(Sheet1[Project #]))

[lbendlin]

Remove the Job # filters, not the Project # filters

[Anonymous]

Project Max Date (Remove Job # filter) = CALCULATE(max(Sheet1[Date]), REMOVEFILTERS(Sheet1[Job #]))

Still not quite right.

[Anonymous]

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!