Conditional Formatting Based on a Table

[petermb72]

I want to dislplay a Card and have the background show red if an occupancy level drops below a certain level.  The twist is that the locations have different thresholds of when they should be red.  Here are the table examples and what I would like for an outcome:

Location	Min Occupancy
North	.90
South	.75
East	.65
West	.50

I would have a slicer for the Location and then a card that would show the current Occupancy rate.  The Card would also show a background color of Red if it drops below the Min. Occupancy.  For an example

North would show a card with Red if the Occupancy was at .88 but none of the other locations would be red, they would be green if they were at .88.  West would be green at .55 but East would be red.  How do I setup up the card to use the different thresholds for the different locations to determin the background color?

[PaulDBrown · Accepted solution]

@petermb72 

With these sample tables:

 and this model:

I've created the following measures:

Occupancy = SUM(FactTable[Occupancy])

Average Min Occ = AVERAGE('Min Occupancy'[Min Occupancy])

and to format the cards:

Conditional format = IF([Occupancy] < [Average Min Occ], 1, 0)

Use this [Conditional format] measure to format the different elements of the card. For example:

Backround

Label

 and you get this

[petermb72]

Does this allow us to choose a location as a slicer rather than the date?

[PaulDBrown]

@petermb72 

Sure...

I've attached the sample PBIX file for your reference

[petermb72]

Also keep in mind I want to use a slicer to change the location and the card to display the correct backgound color, either Green or Red.  I will be doing other cards like this that will include Green Yellow Red depending on the % of a number.