Distinctcount when using the Search function

[Chris2016]

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! 

[PaulOlding · Accepted 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"}
    )

[PaulOlding · Accepted 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"}
    )

[Chris2016 · Accepted solution]

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!