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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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
Mariusz
Community Champion
Community Champion

Hi @Tech325 

 

You can split your Column by Delimiter to rows as per below, and Trim it later.

image.png

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.
LinkedIn

 

Anonymous
Not applicable

Best course would be to extract by delimetere in PowerQuery.
Add Columns > extract text before delimiter > , (this is for first code before ,)
second time > extract text before delimiter  but go to advanced and skip 1 delimiter
Third time > skip 2 delimiter
After that go to transform tab and extract the codes you need from the new columns.
Be sure to use "," as a delimiter and make as many columns as you need. I figure from your data you won't have 1000 of Tech ID's on a single Product ID. so this won't be a problem to modify and it won't cause much problems. After that take the columns and unpivot them, remove blanks and you should be good.

EDIT: don't forget to clean and trim the new unpivoted column!
Capture.PNG

Greg_Deckler
Super User
Super User

I would recommend splitting Technical ID column by comma in Power Query. Then unpivot the resulting columns. Should be pretty easy after that.

@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Thanks for the response.  Very helpful.  I was able to use the delimit function and the unpivot function and got this:  The issue I have is the following: 
Since I'd like to visualize that for Company 123 there is 1 Process B that has 3 Technical IDs, if I try to visualize what I just did, the graph will show that there are 3 Process Bs with 3 Technical IDs.
BEFORE:
Product IDCompanyNature of BusinessProcess AProcess BTechnical IDTechnical ID 2Technical ID 3
325ABCRetail01SAS 234  
246XYZOil & Gas01SAS 234SAS 567 
100123Manufacturing01SAS 234SAS 230SAS 205
112456Insurance10SAS 234  
102ABCBanking01SAS 220  
325XYZRetail01SAS 220  
246123Oil & Gas01SAS 234  
100ABCManufacturing01SAS 234SAS 567 
112XYZInsurance01SAS 234SAS 230SAS 205
102123Banking10SAS 234  
100456Oil & Gas10SAS 220  
AFTER:
Product IDCompanyNature of BusinessProcess AProcess BAttributeValue
325ABCRetail01Technical IDSAS 234
246XYZOil & Gas01Technical IDSAS 234
246XYZOil & Gas01Technical ID 2SAS 567
100123Manufacturing01Technical IDSAS 234
100123Manufacturing01Technical ID 2SAS 230
100123Manufacturing01Technical ID 3SAS 205
112456Insurance10Technical IDSAS 234
102ABCBanking01Technical IDSAS 220
325XYZRetail01Technical IDSAS 220
246123Oil & Gas01Technical IDSAS 234
100ABCManufacturing01Technical IDSAS 234
100ABCManufacturing01Technical ID 2SAS 567
112XYZInsurance01Technical IDSAS 234
112XYZInsurance01Technical ID 2SAS 230
112XYZInsurance01Technical ID 3SAS 205
102123Banking10Technical IDSAS 234
100456Oil & Gas10Technical IDSAS 220
Question: How can I make sure that I can visualize that Company 123 has 1 Process B with 3 Technical IDs?
Thanks!

Hi @Tech325 

I would value Sven_H's suggestion.

Unpivot "Process A" and "Process B", then filter the rows of "1" for the "value" column.

Add columns to the matrix.

Capture15.JPGCapture16.JPGCapture17.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.

Thank you for the screenshots and the time.  This has been very helpful.  One thing I've noticed from your screenshot is that, for example, You have Company 123 with a count of 3 Process Bs when in reality it should only be 2 Process Bs.  Any reason why this would be happening?

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

Hi @Tech325 

Modified:

follow "unpivot columns" suggestion in my previous answer, keep data in this format:

Capture28.JPG

Then create measures

Measure = DISTINCTCOUNT(Query1[Process])

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

count of process = IF(ISINSCOPE(Query1[Nature of Business]),[Measure],[Measure 2])

Capture29.JPGCapture30.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.

 

@v-juanli-msft Thanks for working on this... it's greatly appreciated.  I've noticed that for "Company ABC", if I change the value for Product 325 from "Retail" to "Banking", the output will show that there is only 1 Process B instead of 2 Process Bs.
Any thoughts?
Thanks!
 
BEFORE
Product IDCompanyNature of BusinessProcess AProcess BTechnical IDTechnical ID 2Technical ID 3
325ABCRetail01SAS 234  
246XYZOil & Gas01SAS 234SAS 567 
100123Manufacturing01SAS 234SAS 230SAS 205
112456Insurance10SAS 234  
102ABCBanking01SAS 220  
325XYZRetail01SAS 220  
246123Oil & Gas01SAS 234  
100ABCManufacturing01SAS 234SAS 567 
112XYZInsurance01SAS 234SAS 230SAS 205
102123Banking10SAS 234  
100456Oil & Gas10SAS 220  
 
AFTER
Product IDCompanyNature of BusinessProcess AProcess BTechnical IDTechnical ID 2Technical ID 3
325ABCBanking01SAS 234  
246XYZOil & Gas01SAS 234SAS 567 
100123Manufacturing01SAS 234SAS 230SAS 205
112456Insurance10SAS 234  
102ABCBanking01SAS 220  
325XYZRetail01SAS 220  
246123Oil & Gas01SAS 234  
100ABCManufacturing01SAS 234SAS 567 
112XYZInsurance01SAS 234SAS 230SAS 205
102123Banking10SAS 234  
100456Oil & Gas10SAS 220  
 
 
 
 
 

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.

Anonymous
Not applicable

Unpivot Process A and B. This will merge them together into a column that can just be called "Processes".

Then you can put a table in the visual with Process and Technical ID. Add a slicer that lists companies.

Now you can click on a company in a slicer and see the  filtered information. 🙂

You can't put this in any kind of chart since it has no numerical value so it doesn't show anything.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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

Top Solution Authors
Top Kudoed Authors