Forum Discussion
How to get the conditional formatting for missing combinations
Hello,
I am new to Power BI and would really appreciate your help in solving this issue.
I am trying to add conditional formatting to the values in my matrix.
The problem is I am unable to get the gray and orange colors in values as those cells are combinations of division and merged column that dont exist in data and therefore they are just white color.
The conditional formatting is like this :
Hi nadanur
Power BI cannot apply values - to records that do not exist or to blank entries. A possible workaround is to use separate dimension tables for both row and column categories and assign a value within a measure (usually a zero) when blanks are returned. However, this introduces another issue: it may display values in row/column intersections that should not exist under the current filter context.
Please see the attached pbix.
9 Replies
Hello,
I'm not sure, it's hard to understand. Could you upload a sample pbix?
As I can understand it's a data model issue. Do you have a proper star schema? Divison and merged columns comes from the same table? You should put merged columns in a separate table and set a relationshipThank You!
Can you help me understand like i explained in my new comment on how do i know what data should go in fact and what goes in dim ?
Sure:
The core distinction
Fact tables store:
- Things that happen (events, transactions)
- Numeric values you want to analyze (sales amount, quantity, revenue)
- Foreign keys pointing to dimensions
Dimension tables store:
- Descriptive attributes (who, what, where, when, how)
- Text, categories, labels
- Data used for filtering, grouping, slicing
Take a look at this great piece of microsoft learn
https://learn.microsoft.com/it-it/power-bi/guidance/star-schema
it's well explained
Hi nadanur
Power BI cannot apply values - to records that do not exist or to blank entries. A possible workaround is to use separate dimension tables for both row and column categories and assign a value within a measure (usually a zero) when blanks are returned. However, this introduces another issue: it may display values in row/column intersections that should not exist under the current filter context.
Please see the attached pbix.
Hi, This file was really helpful. However I am unable to get the similar results.
I have now created some dim tables for rows and columns and slicers. When i add this measure from fact table in value, it disrupts the matrix and shows all combinations irrespective of filter. However the same measure without "+0" works fine.
Zones Flag =VAR _Cnt =SUM('Geographic Zones - Employees'[Value])+0RETURN_CntCan you let me know whats wrong?
Create a copy of measure as testmeasure.
Try to add this test measure into the matrix and see what it gets displayed. if it is working per your needs, then go to this next step.
Could you try changing this line of code as red or to the desired color you need? See if it works!-- Fallback"#FFFFFF"if it does not work, try to change the testmeasure to display the values in your calculation: _ZoneCount, then try with _Division, then with _MergedColumn, _DivisionPrefix, _MergedSuffix.
Goal is to identify where the measure is not behaving correctly
this way you know what exactly it is going on and then do the fix.
Thank you for your responses, this gives me some hint on whats going on.
I see the problem is because all the data is coming from same table and therefore it shows blank for those row column combinations that dont exist in table.
If i had to fix it by putting column and rows in separate tables, and to achieve that visual, how do i know what data goes into dim and fact tables.
Any suggestions will be really appreciated.
This is how it works in excel and I am trying to recreate same in Power BI
Hi nadanur ,
Thank you for reaching out to Microsoft Fabric Community and Thanks to Gabry , danextian and sevenhills for Sharing valuable insights.
Just wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions.Best Regards,
Abdul Rafi
Hi nadanur ,
We wanted to check if your question has been resolved or if you are still facing any confusion feel free to reach out.Thank you.
