topic Re: Cumulative Distinct Count by Day Behaving Incorrectly with Filters in DAX Commands and Tips

Cumulative Distinct Count by Day Behaving Incorrectly with Filters

oliverblane72 — Thu, 22 Jul 2021 18:16:57 GMT

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!

Re: Cumulative Distinct Count by Day Behaving Incorrectly with Filters

Anonymous — Thu, 22 Jul 2021 16:09:23 GMT

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

Re: Cumulative Distinct Count by Day Behaving Incorrectly with Filters

Anonymous — Thu, 22 Jul 2021 16:15:39 GMT

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

Re: Cumulative Distinct Count by Day Behaving Incorrectly with Filters

oliverblane72 — Thu, 22 Jul 2021 18:21:45 GMT

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!

Re: Cumulative Distinct Count by Day Behaving Incorrectly with Filters

oliverblane72 — Thu, 22 Jul 2021 18:22:17 GMT

Right, I see! Thank you, Daxer.