Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
Anonymous
Not applicable

Cumulative Totals Chart Filtering Issue

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 #DateCountCategory
111/1/20225Red
122/1/202210Green
213/1/202215Blue

 

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.
kewaynes33_0-1649339927301.png

 

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.

kewaynes33_1-1649339927106.png

 

 

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.

 
kewaynes33_2-1649339927126.png

 

kewaynes33_3-1649339927048.png

 

 

And if I filter down to just Project 1, it gives the correct value of 10...

kewaynes33_4-1649339927189.png

 

kewaynes33_5-1649339927147.png

 

 

However, here's the rub. When I try to add an additional filter context 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. 

kewaynes33_6-1649339927165.png

 

kewaynes33_7-1649339927179.png

 

 

I've been playing around with different combinations of Filter and the ALL functions but I am stumped. Someone else recommened that I use REMOVEFILTERS, but that still wasn't working.

Any help is much appreciated!

 

 

 
1 ACCEPTED SOLUTION

That might be trickier, it depends on how you define "top date". It might work for either earliest or latest dates, I'm not sure. Another option would be to define another measure which ranked it appropriately and use that in the topn filter

View solution in original post

5 REPLIES 5
johnt75
Super User
Super User

On the bar chart place a TOPN filter on the category, based on the Count_ measure.

Anonymous
Not applicable

Thanks for the repsonse, John. If I'm not wanting the Top Count and only wanting the count associated with the Top date for each Project (within the filter context), should I do TOPN based on the date instead? Or are you thinking based on the count?

That might be trickier, it depends on how you define "top date". It might work for either earliest or latest dates, I'm not sure. Another option would be to define another measure which ranked it appropriately and use that in the topn filter

Anonymous
Not applicable

Any thoughts on how I could set up this measure?

Anonymous
Not applicable

Ya, I've gone that route before and the performance was horrible. But it was probably due to my janky DAX. I'll try that route again if the visual level filters aren't working like I want.

 

Thanks for the input.

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors