Forum Discussion

oliverblane72's avatar
oliverblane72
Frequent Visitor
5 years ago
Solved

Cumulative Distinct Count by Day Behaving Incorrectly with Filters

Hi there, I am trying to plot a cumulative distinct count graph of IDs from this table:

I used the following DAX code to generate a cumulative distinct count table by date:

CumTable = SELECTCOLUMNS(
 DISTINCT('Learner Info Daily'[Created Date]),
 "Date", 'Learner Info Daily'[Created Date],
 "CumUsers", CALCULATE(DISTINCTCOUNT('Learner Info Daily'[ID]), FILTER(ALLSELECTED('Learner Info Daily'), 'Learner Info Daily'[Created Date] <= EARLIER('Learner Info Daily'[created date]))
 )
)

Which looks like:

 

However, plotting this with slicers on "Location" does not behave as expected - the cumulative distinct count is the same for each location, the only difference being the x axis (since the dates at which certain locations started getting IDs was different for each):

The only relationship between the two tables is on the date column. I am confused as to where it is going wrong.

 

I appreciate any help on this - thank you!

 

  • Anonymous's avatar
    Anonymous
    5 years ago

     

     

    // Please create a proper calendar in your
// model following the guidelines outlined here:
// https://dax.guide/dateadd
// and connect it to your fact table and remember
// that fact tables should always be hidden and
// slicing should only happen via dimensions. If
// you want to get into trouble, ignore the rule.
//
// Then you can write:

[# Distinct ID to Date] =
var CurrentDate = MAX( Dates[Date] )
return
	CALCULATE(
		DISTINCTCOUNT( 'Learner Info Daily'[LRN] ),
		KEEPFILTERS( Dates[Date] <= CurrentDate ),
		ALLSELECTED( Dates )
	)

     

     

4 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Calculated tables are STATIC. Once calculated, they never change. You need a measure, not a table.

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

     

     

    // Please create a proper calendar in your
// model following the guidelines outlined here:
// https://dax.guide/dateadd
// and connect it to your fact table and remember
// that fact tables should always be hidden and
// slicing should only happen via dimensions. If
// you want to get into trouble, ignore the rule.
//
// Then you can write:

[# Distinct ID to Date] =
var CurrentDate = MAX( Dates[Date] )
return
	CALCULATE(
		DISTINCTCOUNT( 'Learner Info Daily'[LRN] ),
		KEEPFILTERS( Dates[Date] <= CurrentDate ),
		ALLSELECTED( Dates )
	)

     

     

    • oliverblane72's avatar
      oliverblane72
      Frequent Visitor
      5 years ago

      Hi there, I cannot thank you enough - your solution works perfectly!

       

      One thing I am confused about is why we need ALLSELECTED( Dates ). Apologies if this is a silly question, I am quite new to DAX and Power BI.

       

      Many thanks for your help!