topic Distinctcount when using the Search function in DAX Commands and Tips

Distinctcount when using the Search function

Chris2016 — Thu, 08 Dec 2022 16:55:18 GMT

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!

Re: Distinctcount when using the Search function

PaulOlding — Thu, 08 Dec 2022 17:39:44 GMT

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"} )

Re: Distinctcount when using the Search function

Chris2016 — Fri, 09 Dec 2022 07:41:43 GMT

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!