Forum Discussion

husseyma's avatar
husseyma
Frequent Visitor
1 year 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
    CALCULATE (
        DISTINCTCOUNTNOBLANK('Transitions'[Name] ),
        FILTER ( ALL ( 'Transitions' ),'Transitions'[UID] = ThisId)
    )
 
The problem is that I would like to somehow modify the formula so it takes into account my date slicers. At present I am getting the same count irrespective of changing my date slicers because the formula does not take date into account. There is a date column within the "Transitions" table and I have a separate DateDim table also.
 
Ultimately I would like to be able to build a rolling 12 month count of the basis of this data.
Any pointers, greatly appreciated.
 
Thank you!
Need Help

2 Replies

  • rajendraongole1's avatar
    rajendraongole1
    Super User
    1 year 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...