cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Lourini90
Regular Visitor

Cumulative Distinctcount for selected Date and Dimensions

I have that following Table:

BaseTable.PNG

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:

Result.PNG

It should be:

a = 0

b = 1

c = 0

Hope somebody can help me with that.

Thanks a lot!

1 REPLY 1
colacan
Resolver II
Resolver II

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.

Helpful resources

Announcements
Power BI December 2021 Update_carousel 768x460.jpg

Check it Out!

Click here to read more about the December 2021 Updates!

Jan 2022 Dev Camp 768x460 copy.png

Power BI Dev Camp- January 27th, 2022

Mark your calendars and join us for our next Power BI Dev Camp!

UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Top Solution Authors