Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

Reply
Sam_Jask
New Member

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

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!

1 ACCEPTED SOLUTION
Greg_Deckler
Super User
Super User

@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.



Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...

View solution in original post

2 REPLIES 2
v-rzhou-msft
Community Support
Community Support

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
Super User
Super User

@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.



Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Jan NL Carousel

Fabric Community Update - January 2025

Find out what's new and trending in the Fabric community.