Forum Discussion

Chris2016's avatar
Chris2016
Resolver I
3 years ago
Solved

Distinctcount when using the Search function

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...
  • PaulOlding's avatar
    3 years ago

    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's avatar
    Chris2016
    3 years ago

    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!