Forum Discussion
Power BI - way to count rows based on conditional formatting
Hi Flossiebug
As per my understanding of you requirement i have tried using below dax.
1. Create a calculated column using below dax ( If you want to add some more condition then you can in to that).CancellationCategory =SWITCH(TRUE(),SEARCH("ill", 'Table'[Reason], 1, 0) > 0, "Illness",SEARCH("medication", 'Table'[Reason], 1, 0) > 0, "Medication Issue","Other")2. Create measure and then add that measure into the card and the calculated column use it as a slicer.
------> Use below dax to create dax which calculate as per the slicer.Count =COUNTROWS('Table')
If this answers your questions, kindly accept it as a solution and give kudos.
Memorable MemberHi Flossiebug,
You can try this approach to resolve the same:
Create measures instead of relying on conditional formatting:
Step 1: Create a calculated column or measure that categorizes your reasons
Reason Category =
SWITCH(TRUE(),
CONTAINSSTRING([Cancellation Reason], "illness"), "Patient Unwell",
CONTAINSSTRING([Cancellation Reason], "unwell"), "Patient Unwell",
CONTAINSSTRING([Cancellation Reason], "medication"), "Medication Issues",
"Other"
)
Step 2: Create count measures for each category
Patient Unwell Count =
CALCULATE(COUNTROWS(YourTable), [Reason Category] = "Patient Unwell")
Step 3: Add these measures to card visuals on your dashboard
Step 4: Use the same logic in your conditional formatting so colors match your counts
Alternative quick approach:
- Create a slicer with your new "Reason Category" column
- Use a simple COUNTROWS measure that responds to the slicer selection
- This gives you both filtering capability and count display
The key is moving away from conditional formatting as your source of truth and creating proper categorization logic that both your formatting and counting can reference.
Works much better than trying to count based on colors!
Did it work? ✔ Give a Kudo • Mark as Solution – help others too!
Hi - thanks so much for your ideas, I have ended up removing the conditional formatting as I much prefer the new column creating measure to categorise responses and it is definitely much easier than counting the colours! Feeling very new to all this, but learning so much! Thanks again.