Forum Discussion
Avoiding Duplicate counts question
Hi Tech325
In Power query, add a custom column below
Then modify measure
Measure = CALCULATE(COUNT(Query1[Process]),FILTER(Query1,[Text After Delimiter]=BLANK())) Measure 2 = SUMX(ALL(Query1[Nature of Business]),[Measure])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.
- 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 ID Company Nature of Business Process A Process B Technical ID Technical ID 2 Technical ID 3 325 ABC Retail 0 1 SAS 234 246 XYZ Oil & Gas 0 1 SAS 234 SAS 567 100 123 Manufacturing 0 1 SAS 234 SAS 230 SAS 205 112 456 Insurance 1 0 SAS 234 102 ABC Banking 0 1 SAS 220 325 XYZ Retail 0 1 SAS 220 246 123 Oil & Gas 0 1 SAS 234 100 ABC Manufacturing 0 1 SAS 234 SAS 567 112 XYZ Insurance 0 1 SAS 234 SAS 230 SAS 205 102 123 Banking 1 0 SAS 234 100 456 Oil & Gas 1 0 SAS 220 AFTER:Product ID Company Nature of Business Process A Process B Attribute Value 325 ABC Retail 0 1 Technical ID SAS 234 246 XYZ Oil & Gas 0 1 Technical ID SAS 234 246 XYZ Oil & Gas 0 1 Technical ID 2 SAS 567 100 123 Manufacturing 0 1 Technical ID SAS 234 100 123 Manufacturing 0 1 Technical ID 2 SAS 230 100 123 Manufacturing 0 1 Technical ID 3 SAS 205 112 456 Insurance 1 0 Technical ID SAS 234 102 ABC Banking 0 1 Technical ID SAS 220 325 XYZ Retail 0 1 Technical ID SAS 220 246 123 Oil & Gas 0 1 Technical ID SAS 234 100 ABC Manufacturing 0 1 Technical ID SAS 234 100 ABC Manufacturing 0 1 Technical ID 2 SAS 567 112 XYZ Insurance 0 1 Technical ID SAS 234 112 XYZ Insurance 0 1 Technical ID 2 SAS 230 112 XYZ Insurance 0 1 Technical ID 3 SAS 205 102 123 Banking 1 0 Technical ID SAS 234 100 456 Oil & Gas 1 0 Technical ID SAS 220 Question: How can I make sure that I can visualize that Company 123 has 1 Process B with 3 Technical IDs?Thanks!
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.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.
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 ID Company Nature of Business Process A Process B Technical ID Technical ID 2 Technical ID 3 325 ABC Retail 0 1 SAS 234 246 XYZ Oil & Gas 0 1 SAS 234 SAS 567 100 123 Manufacturing 0 1 SAS 234 SAS 230 SAS 205 112 456 Insurance 1 0 SAS 234 102 ABC Banking 0 1 SAS 220 325 XYZ Retail 0 1 SAS 220 246 123 Oil & Gas 0 1 SAS 234 100 ABC Manufacturing 0 1 SAS 234 SAS 567 112 XYZ Insurance 0 1 SAS 234 SAS 230 SAS 205 102 123 Banking 1 0 SAS 234 100 456 Oil & Gas 1 0 SAS 220
