Forum Discussion

John01's avatar
John01
Frequent Visitor
5 years ago

Count values in column with multiple values

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

 

DAX Filtering
Dynamics 365

2 Replies

    • John01's avatar
      John01
      Frequent Visitor
      5 years ago

      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.