Count values in column with multiple values

[John01]

Hi

I have a column called Case Category - however, you can choose multiple Case Categories in this column if you want, so you can end up with Data like below for this Column/Field:

Cat1;Cat2;Cat3

Cat2;Cat3

Cat1

Cat1;Cat3

Cat2

Cat3

Cat1;Cat2;Cat3;Cat4

Cat4

Cat1;Cat4

I need a formula that counts all Cases that have the individual Category - e.g. count all Cases that have Cat1 listed in the category field - the answer to that for column above would be 5. Then do that for all the individual categories.

I was originally thinking I just create a new column with the individual categories and then search for those within the case category column and then count them - so I tried the formula below but returns blank:

CalcCatTotal = CALCULATE(COUNT('incidents'[incidentid]),FILTER(incidents,IF(ISNUMBER(SEARCH(incidents[IndividualCaseCategory],

incidents[casecategory],,Blank())),incidents[casecategory],"")))

This is probably the wrong way to go about it, so if someone could give some suggestions - (fyi: my data is within a Dynamics 365 CRM).

Ta

John

[amitchandak]

@John01 , One way is to split the column into rows. But not sure you want to opt for that way. Also, that will work for import mode

https://www.tutorialgateway.org/how-to-split-columns-in-power-bi/

[John01]

Hi @amitchandak  - i just figured it out, yeah, I had already split them into separate columns, just couldn't figure out how to add up the columns - in the end, I created separate measures to count categories in each split column first, then I created another measure to add the column measures together. Might not be the tidiest method of doing things but got there in the end.