Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

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.

Reply
magnusks
Helper I
Helper I

DIstinctcount based on string in another coloumn

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

TitleCategory

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

1 ACCEPTED SOLUTION
ValtteriN
Super User
Super User

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:

 

ValtteriN_1-1708683111853.png

 

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/





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

2 REPLIES 2
ValtteriN
Super User
Super User

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:

 

ValtteriN_1-1708683111853.png

 

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/





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Thanks! That worked perfectly 🙂

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors