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

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.

Reply
oliverblane72
Frequent Visitor

Cumulative Distinct Count With Slicer Condition

Hi there, I am very new to Power BI and have run into the following issue. I have a table that looks like this:

oliverblane72_1-1626186385155.png

I am looking to perform a cumulative distinct count (in order of date) of the "ID" column. However, I would also like this cumulative distinct count to be dynamic with a slicer on the "Location" column (which has three possible locations). Is this possible?

 

Attempted method: I have a date table in which I calculated the cumulative distinct count of the "ID" column for every single day, which is good as I have been able to plot a graph of this. However, with this method, a slicer on "Location" has no effect on the graph.

 

Thanks in advance!

3 REPLIES 3
ibarrau
Super User
Super User

Hi there. You can try this:

 

CumMeasure = 
CALCULATE (
    DISTINCTCOUNT( Table[ID]);
    FILTER (
        ALLSELECTED( Table );
        Table[Created Date] <= MAX ( Table[Created Date] )
    )
)

 

I think that should work. It might be more effective to involve your date table if you are using one. Adding DateTable in the ALL and using its DateColumns in the filter condition.
I hope that helps,


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

Happy to help!

LaDataWeb Blog

Hi there @ibarrau, thank you very much for your response!

 

If I use what you have suggested in "Table", then every row has the same value which is the total distinct count of the whole table.

 

Yes, I do have a date table. If i place the following in the date table, it gives me the expected cumulative sum, but it is not affected by slicers on the location column.

 

CumMeasure = CALCULATE (
DISTINCTCOUNT ( Table[ID] ),
FILTER (
ALLSELECTED( Table ),
Table[Created Date] <= DateTable[Date]
)
)

 

How might I get a slicer on location to affect these results - or is that not possible since if the cumulative sum is in the date table, then it is in a different table to the location column.

 

Many thanks for your help!

Hi, that's weird. Anyway, if you use date table it has to be like this:

CumMeasure = CALCULATE (
DISTINCTCOUNT ( Table[ID] ),
FILTER (
ALLSELECTED( DateTable ),
DateTable[Date] <= DateTable[Date]
)
)

That should work.
Regards


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

Happy to help!

LaDataWeb Blog

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

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

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors