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.
Hello,
Can someone help with the following issue?
In a table like the below sample, I need to count the number of cars who have "white" and "black" colors.
Car | Color |
BMW | green |
BMW | light-green |
BMW | blue |
BMW | red |
Audy | yellow |
Audy | black |
Audy | blue |
Audy | red |
Audy | white |
Peugeot | blue |
Peugeot | red |
Peugeot | yellow |
Suzuky | black |
Suzuky | green |
Suzuky | light-green |
Suzuky | blue |
Toyota | red |
Toyota | white |
Toyota | blue |
I am using the following calculated column, but instead of DISTINCTCOUNT of cars (3 cars), I am getting the count of the colors (4):
# black & white cars = IF(SEARCH("black", 'Table'[Color], 1, 0) > 0 || SEARCH("white", 'Table'[Color], 1, 0) > 0,CALCULATE(DISTINCTCOUNT('Table'[Car])))
Can you help me understand what I am doing wrong? I need a calculated column or measure that returns just the 3 cars who have the colors I'm searching for.
Thanks!
Solved! Go to Solution.
Hi @Chris2016
The issue is using a calculated column rather than a measure. Counting the distinct Cars needs to be done as a measure.
# black & white cars measure =
CALCULATE(
DISTINCTCOUNT('Table'[Car]),
'Table'[Color] IN {"black", "white"}
)
Hi, @PaulOlding,
Thanks a lot for this simple and practical solution, it works for the scenario that I've given in this post.
However, I was using the Search function because I am able to search parts of the value. E.g. I am searching for "white" in values such as: White, Ghost white, White smoke, White solid, Ash-white etc.
In the sample table I added one more row where the is a Peugeot with Ash-white color. The IN function does not pick it up, but with Search, I am able to get it.
So what I did was take your advice on creating a measure (on top of the calculated column I previously specified) and it gives me the distinctcount of cars that match the specified search criteria:
NewMeasure = CALCULATE([# Cars], 'Table'[# black & white cars]=1)
Thanks a lot!
Hi @Chris2016
The issue is using a calculated column rather than a measure. Counting the distinct Cars needs to be done as a measure.
# black & white cars measure =
CALCULATE(
DISTINCTCOUNT('Table'[Car]),
'Table'[Color] IN {"black", "white"}
)
Hi, @PaulOlding,
Thanks a lot for this simple and practical solution, it works for the scenario that I've given in this post.
However, I was using the Search function because I am able to search parts of the value. E.g. I am searching for "white" in values such as: White, Ghost white, White smoke, White solid, Ash-white etc.
In the sample table I added one more row where the is a Peugeot with Ash-white color. The IN function does not pick it up, but with Search, I am able to get it.
So what I did was take your advice on creating a measure (on top of the calculated column I previously specified) and it gives me the distinctcount of cars that match the specified search criteria:
NewMeasure = CALCULATE([# Cars], 'Table'[# black & white cars]=1)
Thanks a lot!
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 |
---|---|
47 | |
24 | |
20 | |
15 | |
13 |
User | Count |
---|---|
51 | |
46 | |
39 | |
19 | |
19 |