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

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 # 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 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.

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
Super User

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

5 REPLIES 5
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?

Super User

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

#### 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.

#### Power BI Monthly Update - February 2024

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

#### Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

#### 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
Top Kudoed Authors