Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
@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.
User | Count |
---|---|
47 | |
26 | |
22 | |
17 | |
15 |
User | Count |
---|---|
53 | |
34 | |
17 | |
17 | |
15 |