Forum Discussion

husseyma's avatar
husseyma
Frequent Visitor
2 years ago

Distinct Count Column Based on Another Column and Within a Timeframe

I am using the below formula successfully for a calculated column that returns a unique count of names associated with each UID   DCount Name = VAR ThisId = 'Transitions'[UID] RETURN     CALCUL...
Need Help
rajendraongole1's avatar
rajendraongole1
Super User
2 years ago

Hi husseyma - To make your calculated column respond to date slicers, you'll need to modify the formula so that it includes the date context.

DCount Name =
VAR ThisId = 'Transitions'[UID]
VAR SelectedDateRange = CALCULATETABLE(
VALUES('DateDim'[Date]),
ALLSELECTED('DateDim')
)
RETURN
CALCULATE(
DISTINCTCOUNTNOBLANK('Transitions'[Name]),
FILTER(
ALL('Transitions'),
'Transitions'[UID] = ThisId &&
'Transitions'[Date] IN SelectedDateRange
)
)

 

create a measure instead of a calculated column since measures are more dynamic

Rolling 12M DCount Name =
VAR EndDate = MAX('DateDim'[Date])
VAR StartDate = EDATE(EndDate, -12)
RETURN
CALCULATE(
DISTINCTCOUNTNOBLANK('Transitions'[Name]),
FILTER(
ALL('Transitions'),
'Transitions'[UID] = MAX('Transitions'[UID]) &&
'Transitions'[Date] >= StartDate &&
'Transitions'[Date] <= EndDate
)
)

 

Hope it works in your scenerio

husseyma's avatar
husseyma
Frequent Visitor
1 year ago

Thank you so much for your response, unfortunately the modified formula suggested still returns the same result as the original calculation, ie it ignores my date slicers and displays a count for the entire table.  The Rolling 12M also does not appear to be returning relevant results...