Forum Discussion
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 = SELECT...
// 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 ) )
// 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 )
)
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!