Forum Discussion

clarkey1988's avatar
clarkey1988
Helper II
5 years ago
Solved

Distinct Count If's

Hi,

 

I have a calculated column as below. It's calculating what I need. However, I don't want week number or year to be manually typed in. I need it to take the value in that row for both the week number and year columns.

 

Driver 2 Prefix Single DC Runs = CALCULATE(DISTINCTCOUNT('Trip Detail'[ord_hdrnumber]),FILTER('Trip Detail','Trip Detail'[True Driver Type]=1 && 'Trip Detail'[Week Number]=4 && 'Trip Detail'[Year]=2021 && 'Trip Detail'[LegHeaderDriver1]=EARLIER('Trip Detail'[Driver 2 Prefix])))
 
Any help wil be greatly appreciated.
Need Help
  • Ashish_Mathur's avatar
    Ashish_Mathur
    5 years ago

    Hi,

    Does this modified verion work

    =Driver 2 Prefix Single DC Runs = CALCULATE(DISTINCTCOUNT('Trip Detail'[ord_hdrnumber]),FILTER('Trip Detail','Trip Detail'[True Driver Type]=1 && 'Trip Detail'[Week Number]=earlier('Trip Detail'[Week Number]) && 'Trip Detail'[Year]=earlier('Trip Detail'[Year]) && 'Trip Detail'[LegHeaderDriver1]=EARLIER('Trip Detail'[Driver 2 Prefix])))

    Hope this helps.

3 Replies

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    5 years ago

    Hi,

    Does this modified verion work

    =Driver 2 Prefix Single DC Runs = CALCULATE(DISTINCTCOUNT('Trip Detail'[ord_hdrnumber]),FILTER('Trip Detail','Trip Detail'[True Driver Type]=1 && 'Trip Detail'[Week Number]=earlier('Trip Detail'[Week Number]) && 'Trip Detail'[Year]=earlier('Trip Detail'[Year]) && 'Trip Detail'[LegHeaderDriver1]=EARLIER('Trip Detail'[Driver 2 Prefix])))

    Hope this helps.