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.
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 |
Hi Tech325
Modified:
follow "unpivot columns" suggestion in my previous answer, keep data in this format:
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])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 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 AFTERProduct ID Company Nature of Business Process A Process B Technical ID Technical ID 2 Technical ID 3 325 ABC Banking 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 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.