Search code examples
datepowerbiconditional-statementsdaxdimensions

PowerBI Cumulative Distinctcount by Date, Condition and through Dimension

I have the following Table:

BaseTable

It represents cases on which a certain Team is working on over the Time until the case is closed.

And there is also a Date Table over column Date.

I would like to cumulative count the open cases until the selected date.

So I used this measure:

CountOpen =  
VAR CurrentDate = MAX('Date'[Date])
VAR Closed =
CALCULATE(
    DISTINCTCOUNT(Tabelle1[case]),
    ALL('Date'),'Date'[Date]<=CurrentDate,Tabelle1[Status_Open]="0")
VAR OpenAll =
CALCULATE(
    DISTINCTCOUNT(Tabelle1[case]),
    ALL('Date'),'Date'[Date]<=CurrentDate,Tabelle1[Status_Open]="1")
RETURN OpenAll-Closed

And it works for the overall view. But for the view within the Dimension CurrentTeam it's not correct:

Result

It should be:

a = 0

b = 1

c = 0
Solution

  • So... this is actually quite tricky, you have to pick the latest status per case up to the selected date. In my solution I create a table, with a column R which ranks the cases per date, then in the result I filter for those depending on which team you have selected.

    Measure is below:

    VAR CurrentDate = MAX('Date'[Date])
VAR CurrentTeam = SELECTEDVALUE(Tabelle1[CurrentTeam])
VAR tbl = 
    SUMMARIZE(
        FILTER(ALL('Tabelle1'), 'Tabelle1'[Date] <= CurrentDate),
        Tabelle1[case],
        Tabelle1[CurrentTeam],
        Tabelle1[Status_Open],
        Tabelle1[Date],
        "R", 
           VAR c = MAX(Tabelle1[case])
           VAR d = LASTDATE(Tabelle1[Date])
           RETURN 
             CALCULATE(DISTINCTCOUNT(Tabelle1[Date]), 
               ALLSELECTED(Tabelle1), 
               Tabelle1[case] = c, 
               Tabelle1[Date] >= d)
    )
RETURN SUMX(
    FILTER(tbl, 
          [R] = 1 && 
          (ISBLANK(CurrentTeam) || [CurrentTeam] = CurrentTeam) && 
          [Status_Open])
    , 1) + 0 //+0 is here to show 0 where it would be blank