Conditional Formatting in Tables with the Same Underlying Dataset and Different Filter Context

[Sam_Jask]

I am very new to Power BI, and so I apologize for any obvious mistakes here.

I'm working on creating a Power BI dashboard where a user can use slicers to select two model numbers from a database, click a button, and be brought to a page showing the bill of materials for each model in a separate table. To make it easier for users to see what parts are different between models, I want to have conditional formatting to make the background of a component part green if it's contained in both tables, and red if it's only contained in one or the other.

I was able to apply different filter contexts to each table visual using synced slicers, but have been running into a wall trying to figure out how to properly apply conditional formatting. Right now, I have the following calculated column, but can't complete the operation due to memory limitations.

ComponentPresence =
IF(
'First BOM Level'[Order + Line Number] IN VALUES('Selector Table 1'[Selection])

&& 'First BOM Level'[Component Number]

IN CALCULATETABLE(VALUES('First BOM Level'[Component Number]), 'First BOM Level'[Order + Line Number] IN VALUES('Selector Table 2'[Selection])),

"Both",

"One"

)

My idea is to use additional slicers to dynamically filter selector tables containing lookup keys (I can't use the original table with the lookup key because both visuals reference the same table), then use the values from the selector tables to filter the overall table (called "First BOM Level") to then compare the component part numbers in each filter context.

I tried using measures but my understanding is that measures can only store a single value, and so I can't use them to compare multiple component parts across the visuals. Any advice or suggestions or scathing criticisms would be greatly appreciated!

[Anonymous]

Hi @Sam_Jask ,

I agree with  Greg_Deckler, calculated column could not be dynamic. I suggest you to create a measure to achieve your goal.

Your calculation is based on your data model. For further help, I suggest you to share a sample file with us and you can show us a screenshot with the result you want.

This will make it easier for us to find the solution.

Best Regards,
Rico Zhou

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

[Greg_Deckler · Accepted solution]

@Sam_Jask You can't use a calculated column as those are not dynamic, you would need to use a measure and I would suggest a measure that returns "#FF0000" or "#00FF00" and build all the logic into the measure.