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

3 Replies

  • 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.