In Excel, I have a source table (named 'Cumulative Events') with columns Case_num, PT, and Receipt_Date. I have a pivot table with a DAX formula denom = CALCULATE(DISTINCTCOUNT('Cumulative Events'[Case_num]), ALL('Cumulative Events')). When I put PT in the Rows field of my pivot table and denom in the Values field, I get the exact same number in the the resulting denom column (ie, the number of unique values in the Case_num column in the source table). So far, this is exactly the what I want.
However, when I insert a Timeline slider (linked to Receipt_Date) the numbers don't change, and I want them to.
For example, when I use this DAX formula in the Values field, num = DISTINCTCOUNT('Cumulative Events'[Case_num]), the numbers change as I adjust the Timeline. How can I tweak the denom formula so that it too responds to the Timeline slider?
(FYI, my ultimate aim is to use a DAX formula num/denom that is responsive to the Timeline--so both components need to behave that way.)
The ALL function removes all filter context, including any filtering your timeline slicer is doing.
If you want the denominator to respond to the timeline, then you could try ALLSELECTED instead.
denom =
CALCULATE (
DISTINCTCOUNT ( 'Cumulative Events'[Case_num] ),
ALLSELECTED ( 'Cumulative Events' )
)