Forum Discussion

Lourini90's avatar
Lourini90
Frequent Visitor
4 years ago

Cumulative Distinctcount for selected Date and Dimensions

I have that following Table:

It represents cases on wich 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:

It should be:

a = 0

b = 1

c = 0

Hope somebody can help me with that.

Thanks a lot!

1 Reply

  • colacan's avatar
    colacan
    Icon for Resolver II rankResolver II
    4 years ago

    Hi Lourini90, 

    Your measure conducts DISTINCTCOUNT over "Case" which means there are only 2 distinct cases: 1 and 3.

    And it again gets seperated by the filter of Calculate: 

        Tabelle1[Status_Open]="0" and 
        Tabelle1[Status_Open]="1"

     

    That means, whatever the context is, the maximum value of "Oepn_All" or "Closed" cannot be bigger than 4:

    Case1 & Tabelle1[Status_Open]="0"

    Case1 & Tabelle1[Status_Open]="1"

    Case3 & Tabelle1[Status_Open]="0"

    Case3 & Tabelle1[Status_Open]="1"

    ==> Total 4 distinct cases.

     

    If you understood, you would understand why the team "a" has the value of 2.

     

    It's becuase the team "a" has 2 distinct cases as below. (you can easily check it by filtering your table with team "a")


    Case1 & Tabelle1[Status_Open]="1"   

    Case3 & Tabelle1[Status_Open]="1" (there 2 cases like this but number of distinct case is 1)

    This is why your table shows 2 for team "a"

     

    I hope this helps you.