Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Distinct count

I am trying to return distinct count from my table using sysdate- 30 and 60 and 90 days in three different measure. Can someone advise me the easy way to achieve this?
General Comment
Interesting Links
Need Help
Tips and Tricks
  • az38's avatar
    az38
    6 years ago

    hi Anonymous 

    try

    Measure90 = CALCULATE(DISTINCTCOUNT(Table1[Column1]);ALL(Table1);Table1[Date]>=(Today()-90))
Measure60 = CALCULATE(DISTINCTCOUNT(Table1[Column1]);ALL(Table1);Table1[Date]>=(Today()-60))
Measure30 = CALCULATE(DISTINCTCOUNT(Table1[Column1]);ALL(Table1);Table1[Date]>=(Today()-30))

    do not hesitate to give a kudo to useful posts and mark solutions as solution
    Linkedin

az38's avatar
az38
Community Champion
6 years ago

hi Anonymous 

try

Measure90 = CALCULATE(DISTINCTCOUNT(Table1[Column1]);ALL(Table1);Table1[Date]>=(Today()-90))
Measure60 = CALCULATE(DISTINCTCOUNT(Table1[Column1]);ALL(Table1);Table1[Date]>=(Today()-60))
Measure30 = CALCULATE(DISTINCTCOUNT(Table1[Column1]);ALL(Table1);Table1[Date]>=(Today()-30))

do not hesitate to give a kudo to useful posts and mark solutions as solution
Linkedin

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    That was apt. Thanks a ton.

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    az38what if i have to filter this further? for example;

     

    Date              RJC#   ACP#

    1/3/2019         0         1

    1/3/2019         1         0

    1/2/2019         0         1

    1/2/2019         1         0

    1/2/2019         1         0

    1/1/2019         0         1

    1/1/2019         1         0

    1/1/2019         1         0

    1/1/2019         1         0

     

    Keeping the earlier 30, 60 90 days in filters, what we want to find out id the count of one RJC: for every date within last 30, 60 90 days, we need to count RJC# where RJC# for a given date should be less than or equal to 1. 

     

    for 1/1/2019, RJC# is 3 and 4th one is ACP# so it should be treated as 0. Where as for 1/3/2019, RJC# is 1 so it should be treated as 1.

     

    Pls advise.