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 have a question to only distinct count values that is not duplicate.
For example i have a column with "apple", "apple", "banana", "orange", and "cucumber". I want to return the value of 3 because "apple" has duplicate value.
My measure is
Count =
Var Column = 'Table'[Column]
RETURN
CALCULATE(
DISTINCTCOUNT(Column),
ALLSELECTED(Column)
)
It doesnt work for me. Thanks a lot
Solved! Go to Solution.
Hi,
Thank you for your message.
I am not sure how your desired outcome of the visualization looks like, but please check the below picture and the attached pbix file.
Count distinct only measure: =
COUNTROWS ( FILTER (DISTINCT( Data[Name] ), CALCULATE ( COUNTROWS ( Data ) ) = 1 ) )
If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.
Hi,
I am not sure how your data model looks like, but please check the below picture and the attached pbix file.
Count distinct only measure: =
COUNTROWS ( FILTER ( Data, CALCULATE ( COUNTROWS ( Data ) ) = 1 ) )
If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.
Hi Kim,
Really thanks for the pbix. I am still wondering how you can make a measure with the reference of Data (Table name) not category as the column name. I have so many columns, and i want to make measure to return a value of 3.
On my case i want the Fruit return a measure of 3 and Vegetable return value of 2 . Really thanks
Let say
Category | Name |
Fruit | Apple |
Fruit | Apple |
Fruit | Banana |
Fruit | Orange |
Fruit | Strawberry |
Vegetable | Cucumber |
Vegetable | Cauliflower |
Vegetable | Broccoli |
Vegetable | Broccoli |
Hi,
Thank you for your message.
I am not sure how your desired outcome of the visualization looks like, but please check the below picture and the attached pbix file.
Count distinct only measure: =
COUNTROWS ( FILTER (DISTINCT( Data[Name] ), CALCULATE ( COUNTROWS ( Data ) ) = 1 ) )
If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.
Really thanks,
It works now. Happy New Year
Hi,
Thanks for your very prompt help, although it shows no error but it produce blank row in my case.
Any idea where i might have mistaken?
Thank you so much
Hi @dannytan1112
Not sure about the current filter context but you may also try
Count =
SUMX (
ALLSELECTED ( 'Table'[Column] ),
IF ( COUNTROWS ( CALCULATETABLE ( 'Table' ) = 1, 1 )
)
you can try
Count =
SUMX (
VALUES ( 'Table'[Column] ),
IF (
COUNTROWS ( CALCULATETABLE ( 'Table' ) ) = 1,
1
)
)
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 |
---|---|
46 | |
28 | |
22 | |
12 | |
8 |
User | Count |
---|---|
75 | |
52 | |
46 | |
15 | |
12 |