Distribute COUNT for Types seperated by /

[AmiraBedh]

I have the following table :

COI Type	COI ID
Client vs Client	125
Client vs N	5636
B vs N Client	54
Client vs Client/B vs N Client	51
Client vs Client	4
Client vs Client/B vs N Client	5
Client vs N	31
B vs N Client/ Client vs N	45

I want to calculate the number of OP ID by  OP Type using this condition :

When for example we have :

a type seperated by / like Client vs Client/B vs N Client   

the count will be like below 1 goes to Client vs Client

and 1 goes to B vs N Client 1 

and 1 remains  for Client vs Client/B vs N Client

The output will be :

 

OP	Count
Client vs Client	4
Client vs N	3
B vs N Client	4
Client vs Client/B vs N Client	2
B vs N Client/ Client vs N	1

[AmiraBedh · Accepted solution]

I ended up using this solution :

Special Count = 
  COUNTROWS(
    FILTER(
      ALLSELECTED('YourTable'),
      CONTAINSSTRING('YourTable'[OP Type], SELECTEDVALUE('YourTable'[OP Type]))
    )
  )

powerbi - Distribute COUNT for Types seperated by / - Stack Overflow

[amustafa]

I had to trim and clean your sample data column "COI Type" beacsue it contained some trailing spaces. Anyhow, I split the values where it found a slash "/" them merged it in Power Query. See the M code and results below. You can aslo download my work files.

let
    Source = Excel.Workbook(File.Contents("C:\Users\aliom\OneDrive\Power BI Samples\Unique Type ID count\Sample.xlsx"), null, true),
    Table1_Table = Source{[Item="Table1",Kind="Table"]}[Data],
    #"Changed Type" = Table.TransformColumnTypes(Table1_Table,{{"COI Type", type text}, {"COI ID", Int64.Type}}),
    #"Trimmed Text" = Table.TransformColumns(#"Changed Type",{{"COI Type", Text.Trim, type text}}),
    #"Cleaned Text" = Table.TransformColumns(#"Trimmed Text",{{"COI Type", Text.Clean, type text}}),
    #"Split Column by Delimiter" = Table.ExpandListColumn(Table.TransformColumns(#"Cleaned Text", {{"COI Type", Splitter.SplitTextByDelimiter("/", QuoteStyle.Csv), type list}}), "COI Type"),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"COI Type", type text}})
in
    #"Changed Type1"

Download link:

Unique Type ID count

If I answered your question, please mark this thread as accepted and Thums Up!
Follow me on LinkedIn:
https://www.linkedin.com/in/mustafa-ali-70133451/