Conditional format is not displayed

[Jan_Trummel]

Hello to the forum,

I have a problem and hope one of you has a brilliant idea to solve it.

I have these 2 tables:

My tables

Both tables are connected by a 1-N relationship:

Both tables are related via the "ID"-columns

In the report I created a table visualization in which I output all columns of the Data-table:

Table visual with "ID" and "Check" from the Data-table

In the Check column I have set a conditional formatting:
⦁ If "OK" then green check mark
⦁ If "0" then red X

The conditional format for the column "Check"

Everything works great. But now I drag the Value field from the Details-table. Now the row with ID 4713 disappears. This is logical because there is no matching foreign key value in the details table.

ID "4713" is missing now

So I turn on the option Show items with no data so that the data set appears again. But now I have the problem that the conditional formatting for this row is no longer displayed.

The conditional format for ID "4713" is missing

The reason for this is that the conditional formatting uses the "First" option to search for a value in the Check column. However, it does not find anything for the record with ID 4713, since this row is not actually included in the join between the two tables. I can also visualize this by dragging Check into the table again and setting the "First" option:

First Check finds nothing for the ID "4713"

I am now looking for an option to display the conditional formatting for the record with ID 4713. Does anyone have an idea?

Best regards!

[Anonymous]

Hi @Jan_Trummel ,

You can use the NATURALLEFTOUTERJOIN function to create the following table, and then use the conditional formatting in the table to match your actual scenario.

status = 
IF (
    ISBLANK ( MAX ( 'Table'[Value] ) ),
    "yellow",
    IF ( MAX ( 'Table'[Check] ) = "OK", "green", "red" )
)

Best Regards,
Adamk Kong

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Jan_Trummel]

Hello @Anonymous,

thank you for your answer! This is definitely a possible solution to achieve a goal!

However, I am creating a new table in the model, which means that Power BI has to save the data twice.

My ideal solution would be to use a measure to get Power BI to display the conditional formatting for the data set with ID 4713.

To think very naively: I would need a logic that writes a 1 if the Check column says "OK", otherwise a 0. I would no longer have to access the Check column via the First method.

I created this measure for that:

Show =
IF(
    SELECTEDVALUE(Data[Check]) = "OK",
    1,
    0
)

However, if I drag this measure into the table visualization, I destroy the JOIN between the two tables. Power BI then creates the Cartesian product, that means it connects every record from the Data table with every record from the Details table:

The measure creates the Cartesian product ("Show items with no data" is activated)

If I deactivate the option Show items with no data, then at least the rows in which Power BI does not output any conditional formatting are removed.

If I deactivate "Show item with no data" I still get the Cartesian product (but at least fewer lines)

Do you have any idea how I can create the logic described above in a measure without Power BI creating the Cartesian product?