Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount.
Register nowThe Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.
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!
Solved! Go to 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 )
)
// 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!
Calculated tables are STATIC. Once calculated, they never change. You need a measure, not a table.
Right, I see! Thank you, Daxer.
User | Count |
---|---|
21 | |
19 | |
12 | |
9 | |
8 |
User | Count |
---|---|
30 | |
26 | |
15 | |
13 | |
10 |