Forum Discussion
Power BI - way to count rows based on conditional formatting
Hi all - I'm currently trying to develop a simple dashboard for use at work in my NHS department.
I have a table of reasons for cancellations - so it's free text format. I've applied conditional formatting to identify particular reasons, such as illness, or medication issues which colours things a certain colour based on reason.
What I'd really like to do is to be able to add a card or filter which will show me a quantitative value for each reason - so all those identified as being due to the patient being unwell, which conditional formatting has coloured grey, is counted as a number and displayed on a card.
Is this something which is achievable? I'm very new to Power BI so have been drowning in Google and videos trying to find a solution!!
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.
8 Replies
- bhanu_gautam
Super User
Flossiebug Create a new measure in Power BI that counts the number of cancellations for a specific reason. You can use the DAX COUNTROWS function combined with a FILTER function to achieve this.
Illness Cancellations = COUNTROWS(FILTER('YourTableName', 'YourTableName'[CancellationReason] = "Illness"))
Once you have created measures for each reason, you can add a card visualization to your Power BI report canvas. Drag the measure you created (e.g., Illness Cancellations) to the "Values" field of the card visualization to display the quantitative value for each reason.
Hi - thanks for your response. I've gone with a different solution, but can see how yours would help too. Thanks so much for your time and reply.
- jaineshp
Memorable Member
Hi 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 dashboardStep 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.
- FBergamaschi
It is feasible, pls provide tables
Please include, in a usable format, not an image, a small set of rows for each of the tables involved in your request and show the data model in a picture, so that we can import the tables in Power BI and reproduce the data model. The subset of rows you provide, even is just a subset of the original tables, must cover your issue or question completely. Do not include sensitive information and do not include anything that is unrelated to the issue or question. Please show the expected outcome based on the sample data you provided and make sure, in case you show a Power BI visual, to clarify the columns used in the grouping sections of the visual.
Need help uploading data? click here
Want faster answers? click here
Hi - thanks for your answer - I will include more info next time as an example, making sure to keep it anonymised.
- mdaatifraza5556
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.Oh wow - thank you so much! I've tried your ideas and it has done exactly what I was looking for, thank you so so much. I'm loving finding out what I can do with Power BI and am really grateful to you for taking the time to explain it all so clearly.
