Forum Discussion

Alvarom1's avatar
Alvarom1
Helper I
6 years ago
Solved

Help on DAX-DISTINCTOUNT

Hi I want to calculate a number of distinctcount for this table   Letter Color a White a Red b White c White c Red d Red e White f   g White g Red i ...
Need Help
  • v-lili6-msft's avatar
    v-lili6-msft
    6 years ago

    hi, Alvarom1 

    You could try this way as below to get it:

    Step1:

    Create a measure that DISTINCTCOUNT different colors of each letter.

    different colors each Letter = CALCULATE(DISTINCTCOUNT('Table'[Color]))

    Step2:

    Create a measure that DISTINCTCOUNT while colors of each letter.

    Only white each letter = CALCULATE(DISTINCTCOUNT('Table'[Letter]),FILTER('Table','Table'[Color]="White"))

    Step3:

    Then use this measure to get your requirement:

    Result = CALCULATE(DISTINCTCOUNT('Table'[Letter]),FILTER(VALUES('Table'[Letter]),[different colors each Letter]=[Only white each letter]))

    Result:

     

    and here is sample pbix file, please try it.

     

    Regards,

    Lin

Alvarom1's avatar
Alvarom1
Helper I
6 years ago

Thanks again, but with that I do not get the expected result that is the distinct count of letters that have ONLY "White", excluding the letters that have "White" and "Red" , that in this table are letters "b" and "e", that is 2 letters. Your formula gives me 5 as result but what I expect is 2

 

Makes it sense?

 

Thanks again!

 

Alvaro 

 

amitchandak 

v-lili6-msft's avatar
v-lili6-msft
Community Support
6 years ago

hi, Alvarom1 

You could try this way as below to get it:

Step1:

Create a measure that DISTINCTCOUNT different colors of each letter.

different colors each Letter = CALCULATE(DISTINCTCOUNT('Table'[Color]))

Step2:

Create a measure that DISTINCTCOUNT while colors of each letter.

Only white each letter = CALCULATE(DISTINCTCOUNT('Table'[Letter]),FILTER('Table','Table'[Color]="White"))

Step3:

Then use this measure to get your requirement:

Result = CALCULATE(DISTINCTCOUNT('Table'[Letter]),FILTER(VALUES('Table'[Letter]),[different colors each Letter]=[Only white each letter]))

Result:

 

and here is sample pbix file, please try it.

 

Regards,

Lin

  • Alvarom1's avatar
    Alvarom1
    Helper I
    6 years ago

    It works!

    Thanks a lot

    Alvaro

  • Alvarom1's avatar
    Alvarom1
    Helper I
    6 years ago

    HI Again,

    as I confirmed, it works.

    The issue that I have now is that the database is big and when it comes to calculate the last measure it takes a long time (several minutes) until the result appears.

    Any idea to make it faster?

    Thanks

    Alvaro

     

    v-lili6-msft