Cumulative Distinct Count by Day Behaving Incorrectly with Filters

[oliverblane72]

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 · Accepted solution]

// 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]

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!

[Anonymous]

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

[oliverblane72]

Right, I see! Thank you, Daxer.