Improper Aggregation based on Slicer Selection (Descending Cumulative Total)

[djkoenig]

Hello Experts,

I'm quite confused. I have calculated a descending running total like so:

DescendingCumulativeSum = 
VAR MaxDate = MAX('Patent List'[Earliest Priority Date])
VAR TotalCount = COUNTROWS(
    FILTER(
        ALL('Patent List'),
        'Patent List'[Dead or Alive] = "DEAD" &&
        'Patent List'[ExpiredPatentCountCategory] <> Blank()
    )
)
RETURN
    TotalCount - 
    CALCULATE(
        COUNTROWS('Patent List'),
        FILTER(
            ALL('Patent List'),
            'Patent List'[Earliest Priority Date] <= MaxDate &&
            'Patent List'[Dead or Alive] = "DEAD" &&
            'Patent List'[ExpiredPatentCountCategory] <> Blank()
        )
    ) + [_ExpiredCount]

 This seems okay and I get my expected output:

So, what I'd like to do is give the user optionality to filter between Assignee Companies. If I create a slicer for this, it works.... but only if I select a single company. Selecting two or more companies creates some weird aggregation.

To combat, I added a VALUES clause, as I thought that might alleviate the issue. No dice.

DescendingCumulativeSum_Select = 
VAR CurrentDate = MAX('Patent List'[Earliest Priority Date])
VAR SelectedCompanies = VALUES('Patent List'[Assignee Company])
VAR TotalCount = COUNTROWS(
    FILTER(
        ALL('Patent List'),
        'Patent List'[Dead or Alive] = "DEAD" &&
        'Patent List'[ExpiredPatentCountCategory] <> BLANK() &&
        'Patent List'[Assignee Company] IN SelectedCompanies
    )
)
RETURN
    TotalCount - 
    CALCULATE(
        COUNTROWS('Patent List'),
        FILTER(
            ALL('Patent List'),
            'Patent List'[Earliest Priority Date] <= CurrentDate &&
            'Patent List'[Dead or Alive] = "DEAD" &&
            'Patent List'[ExpiredPatentCountCategory] <> BLANK() &&
            'Patent List'[Assignee Company] IN SelectedCompanies
        )
    ) + [_ExpiredCount]

where _ExpiredCount = Calculate(Count('Patent List'[Original Patent Number]), 'Patent List'[ExpiredPatentCountCategory] <> Blank(), 'Patent List'[Dead or Alive] = "DEAD")

However, if I hard-code the company name into the logic then it works.

DescendingCumulativeSum_Specific = 
VAR CurrentDate = MAX('Patent List'[Earliest Priority Date])
VAR TotalCount = COUNTROWS(
    FILTER(
        ALL('Patent List'),
        'Patent List'[Dead or Alive] = "DEAD" &&
        'Patent List'[ExpiredPatentCountCategory] <> BLANK() &&
        'Patent List'[Assignee Company] IN {"Hitachi Metals", "TDK", "Shin-Etsu"}
    )
)
RETURN
    TotalCount - 
    CALCULATE(
        COUNTROWS('Patent List'),
        FILTER(
            ALL('Patent List'),
            'Patent List'[Earliest Priority Date] <= CurrentDate &&
            'Patent List'[Dead or Alive] = "DEAD" &&
            'Patent List'[ExpiredPatentCountCategory] <> BLANK() &&
            'Patent List'[Assignee Company] IN {"Hitachi Metals", "TDK", "Shin-Etsu"}
        )
    ) + [_CountSpecific]

The red is DescendingCumultiaveSum_Select, while the green is DescendingCumulativeSum_Specific. 

Can someone help me understand what is happening here? Hoping I am overlooking something obvious. 

Thank you! 

[djkoenig]

Oh, I was doing something quite goofy... Asking it to filter based on slicer selection, then turning around and immediately stripping the filter context in the measue.

Thanks for reteaching me the ALLSELECTED lesson @v-xianjtan-msft, much appreciated 🙂

[v-xianjtan-msft · Accepted solution]

Hi @djkoenig 

The problem with aggregating the DescendingCumulativeSum measure in the matrix after applying the slicer is due to the ALL() function used in DAX removing all the filters from the data table 'Patent List', please use the ALLSELECTED() function instead to preserve the effect of the external filters.

DescendingCumulativeSum = 
VAR MaxDate = MAX('Patent List'[Earliest Priority Date])
VAR ExpiredCount = CALCULATE(COUNT('Patent List'[ID]), 'Patent List'[Dead or Alive] = "DEAD")
VAR TotalCount = COUNTROWS(
    FILTER(
        ALLSELECTED('Patent List'),
        'Patent List'[Dead or Alive] = "DEAD"
    )
)
VAR CurrentCount = CALCULATE(
    COUNTROWS('Patent List'),
    FILTER(
        ALLSELECTED('Patent List'),
        'Patent List'[Earliest Priority Date] <= MaxDate &&
        'Patent List'[Dead or Alive] = "DEAD"
    )
)
RETURN
TotalCount - CurrentCount + ExpiredCount

Best Regards,
Jarvis Tang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[tharunkumarRTK]

@djkoenig 

can you share a pbix file with sample data and expected result?

[djkoenig]

Hello @tharunkumarRTK , 

Sorry for the delay, there was a bit of confidential information I had to remove. The goal is simple (I thought). I want my measure to calculate the amount of patents remaining based on the earliest priority year. I want this measure to be affected by slicer selection, mainly by Assignee Company. 

Sample PBIX is linked here: DescendingCumulativeSum.PBIX 

I have to be doing something goofy. Let me know if I can provide anything else. And I really do appreciate you taking some time to look at this. Thank you 🙂