Forum Discussion

Tech325's avatar
Tech325
New Member
6 years ago
Solved

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 rela...
Need Help
  • v-juanli-msft's avatar
    v-juanli-msft
    6 years ago

    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.

Greg_Deckler's avatar
Greg_Deckler
Community Champion
6 years ago
I would recommend splitting Technical ID column by comma in Power Query. Then unpivot the resulting columns. Should be pretty easy after that.
Tech325's avatar
Tech325
New Member
6 years ago
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!
  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    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.

  • v-juanli-msft's avatar
    v-juanli-msft
    Community Support
    6 years ago

    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.

    • Tech325's avatar
      Tech325
      New Member
      6 years ago

      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  
      • v-juanli-msft's avatar
        v-juanli-msft
        Community Support
        6 years ago

        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.