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 = SELECT...
  • 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 )
    	)