Forum Discussion

oliverblane72's avatar
oliverblane72
Frequent Visitor
5 years ago

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:

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

  • ibarrau's avatar
    ibarrau
    Super User
    5 years ago

    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,

    • oliverblane72's avatar
      oliverblane72
      Frequent Visitor
      5 years ago

      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!

      • ibarrau's avatar
        ibarrau
        Super User
        5 years ago

        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