Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 | Red |
and i need to calculate the distinct count of letters that have onle "White". In this simple case the result it would be 2 (letters b & e).
Can you help me on using that to make the calculation with DAX?
Many thanks
Alvaro
Solved! Go to Solution.
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
Try
calculate(DISTINCTCOUNT(letter),color="White")
Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks.
Thanks for your reply. But with that the result is 5 as it includes the letters that have "White" but also "Red". The result that I am looking for is 2, only letters that have "White" and no other color.
Hope is clear. Any suggestion?
Thanks!
Alvaro
That should filter white
calculate(DISTINCTCOUNT(letter),color="White")
Other solution
calculate(DISTINCTCOUNT(letter),filter(table,color="White"))
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
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
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
It works!
Thanks a lot
Alvaro
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
105 | |
97 | |
80 | |
67 | |
63 |
User | Count |
---|---|
146 | |
110 | |
107 | |
86 | |
63 |