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
Tech325
New Member

Avoiding Duplicate counts question

I have the below table and would like to visualize the following:

  • To calculate the number of unique Technical IDs in relation to Process A and Process B
    • For example, there are 6 SAS 234s in relation to Process B however, Power BI will visualize that there are only 2 SAS 234s since the others have other values in the cell (SAS 234, SAS 567, etc.)
    • I can't get Power BI to take the values in the cell that are separated by commas.  
    • Do I nned to remove the commas and create a separate column?

Any idea??

 

Product IDCompanyNature of BusinessProcess AProcess BTechnical ID
325ABCRetail01SAS 234
246XYZOil & Gas01SAS 234, SAS 567
100123Manufacturing01SAS 234, SAS 230, SAS 205
112456Insurance10SAS 234
102ABCBanking01SAS 220
325XYZRetail01SAS 220
246123Oil & Gas01SAS 234
100ABCManufacturing01SAS 234, SAS 567
112XYZInsurance01SAS 234, SAS 230, SAS 205
102123Banking10SAS 234
100456Oil & Gas10SAS 220
1 ACCEPTED SOLUTION

Hi @Tech325 

In Power query, add a custom column below

Capture13.JPG

Then modify measure

Measure = CALCULATE(COUNT(Query1[Process]),FILTER(Query1,[Text After Delimiter]=BLANK()))

Measure 2 = SUMX(ALL(Query1[Nature of Business]),[Measure])

Capture14.JPG

Best Regards
Maggie
Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

10 REPLIES 10

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