DIstinctcount based on string in another coloumn

[magnusks]

Hi,

I have a table named Projects, that contains a list of projects and various data related to those projects. Some projects are listed multiple times (intentional) due to multiple application processes. One of the coloumns (Category) in my table specifies the type of project and are seperated by a delimiter ( ; ).

Table: Projects

Title	Category
A	Health;Other;Registry
B	Other
C	Health;Registry
C	Health;Registry
D	Registry
D	Registry
E	Health
E	Health
F	Other; Registry
G	Other
H	Registry

I want to calculate the amount of projects that are categorized as "Health". I tried this DAX (below):

Measure = 
   CALCULATE( 
        DISTINCTCOUNT(Projects[Title]), FILTER(Projects, Projects[Category]="Health"))

This returns 1 and not 3 which is what I want. I guess the problem is that it searches in rows that contains only Health and not those that contains Health in addition to others (Health;Other;Registry). 

I tried to add SEARCH and CONTAINSSTRING into the DAX but cant get it to work. 

Any suggestions?

Thanks

[ValtteriN · Accepted solution]

Hi,

You were on the right track. Here is an example on how to make this work:

Measure 18 = COUNTROWS(

        DISTINCT(

    SELECTCOLUMNS(

    FILTER('Table (25)',CONTAINSSTRING([Category],"Health")),'Table (25)'[Title])))

End result:

Here we first filter the table to contain only rows with "Health". Then we select the distinct categories. Finally COUNTROWS gives us the value we want:

I hope this post helps to solve your issue and if it does consider accepting it as a solution and giving the post a thumbs up!

My LinkedIn: https://www.linkedin.com/in/n%C3%A4ttiahov-00001/

[magnusks]

Thanks! That worked perfectly 🙂