Problem with Cell Elements Formatting

[MJG2112]

Hi. I'm not getting the desired results with Cell Elements formatting.  My scenario is this:

I have two tables A & B.  They have a Many:Many relationship, but with the correct application of filters this becomes 1:1 (or 1:none, which appears to be the root of my problem). A filters B.

I have Cell Element formatting on two columns from table A, and, because I want to display a row for every record in table A, I have ‘Show items with no data’ ticked. This display all the records I want from A, however any with no corresponding record in table B do not have the Cell Element formatting applied to the table A columns.

Is this just a ‘feature’ of Power BI that I have to live with or can I fix this?

[MJG2112]

@v-hashadapu Thank you so much for your patience and help. That worked!

[v-hashadapu]

Hi @MJG2112 , hope you are doing great. May we know if your issue is solved or if you are still experiencing difficulties. Please share the details as it will help the community, especially others with similar issues.

[MJG2112]

Hi @v-hashadapu, I still can't get this to work.  Using a measure only returns the same state for every row in table (so every row has the same icon), except the rows with nothing in in table B, which remain blank.

[v-hashadapu · Accepted solution]

Hi @MJG2112 , Thank you for reaching out to the Microsoft Community Forum.

Make sure the table visual includes a unique A key so the measure can evaluate per row. Then add this test measure to the visual, you should see 1 or 0 for each A row:

HasBFlag =

IF( CALCULATE( COUNTROWS( TableB ) ) > 0, 1, 0 )

Apply conditional formatting. Conditional formatting -> Icons (or Background) -> Format style = Rules -> What field should we base this on? = HasBFlag -> Summarization = First. Create two rules: 1 => green icon, 0 => red icon. Don’t return BLANK(), use 0 for missing-B. If A->B filtering is unreliable, use the explicit key variant that matches A’s key to B’s key.

If the test measure still shows the same value for every row, paste the exact DAX you used and a screenshot of the table with the test measure visible and anything you may feel relevant to the issue.

[MJG2112]

@amitchandak I think I might not be explaining very well. I already have rule based formatting as you can see. It's based on the column being formatted, which is from Table A and is not blank or null.  It has 'Yes' and 'No' values.  The problem is it's not being applied to those rows that don't have a record in Table B. I would expect the Level OK column on the second row and bottom 2 rows in the other screenshoot to have the icon displayed.

[v-hashadapu]

Hi @MJG2112 , Thank you for reaching out to the Microsoft Community Forum.

@amitchandak  is right that you can detect the presence of a related row in Table B with an aggregate like MAX(TableB[Outcome]) and that conditional formatting should be driven by a measure when the visual is built from Table A but the row exists or not information lives in Table B.

When a row from A has no match in B, the formatting engine ends up evaluating the rule against an empty related context. Even though Level OK itself contains Yes/No, the rule engine still gets a blank formatting value and Power BI treats that as don’t apply any icon. That’s why those A only rows don’t show your green/red icons. The fix is to base the formatting on a measure that always returns a concrete value. For example:

LevelOK_IconFlag =

IF( NOT ISBLANK( MAX( TableB[Outcome] ) ), 1, 0 )

or a colour version if you prefer field value formatting. Point your icon rules at this measure instead of the raw Level OK column and map 1 and 0 to the icons you want. Because the measure returns a definite value for both cases, the icons will show even when Show items with no data is bringing in rows that have no B record.

[MJG2112]

@amitchandak Thanks for your swift response.  I don't understand what you mean, so I've attached a screen shot to illustrate the problem more clearly. The columns up to Level 3 are from Table A. The Outcome column is from table B. The rows with a blank Outcome have no record in Table B and, as you can see, the cell element formatting has not been applied to columns Level 2 and Level OK.

[amitchandak]

@MJG2112 , We need a measure like 
If( Max(TableB[Outcome]) = blank() , Blank(), TableA[OK])
Now using this measure try to do conditional formatting and see if that can help 

If this does not help
Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

[amitchandak]

@MJG2112 , Only rule-based and gradient can handle null, make sure you exclude 0 from the rule. So create a measure that returns a color or a number that you can associate in a rule for color and handle null values. DO not handle 0 
Simple example null in red, value in green 

[amitchandak]

@MJG2112 , Cell formatting in the table visual can happen on all fields. you can do it based on the measure using field value option. You can use the max value of the Table B's column  if needed.