Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
John01
Frequent Visitor

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

 

2 REPLIES 2
amitchandak
Super User
Super User

@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/

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.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors