Conditional formatting with hierarchies

[Anonymous]

Hi,

I have a bar chart visual with the following heirachial levels

Level 1 - Country data

Level 2 - Province data

Level 3 - City data

The calculation is a percentage out of 100% with the Goal line at 85%.

Following are the scores

1. City1, Province1: 84%, City2, Province1: 89%, City3, Province1: 87%
2. City1, Province2: 86%, City2, Province2: 91%, City3, Province2: 88%

As per the above data City1, Province1 is failing, but if I plot the bar chart, the Level 1 (Country) and Level 2 (Province1) for Province 1 is displaying "Green" (conditional formatted). 

Question: Is it possible that Level 1 (Country) and Level 2 (Province1) is displayed "Red" if any data at the lower level heirarchy is failing.

Please advise.

[mwegener · Accepted solution]

Hi @Anonymous ,

I think you can use MINX to create a measure that gives you the lowest percentage from Level 3 (City).

This Measure you can use for your conditional formatting.

https://docs.microsoft.com/en-us/dax/minx-function-dax

Regards,

Marcus

Dortmund - Germany
If I answered your question, please mark my post as solution, this will also help others.
Please give Kudos for support.

[v-jayw-msft · Accepted solution]

Hi @Anonymous 

You can create the measure below to get the min value of the country and add it to the Conditional Formatting Rules.

Measure = MINX('Table','Table'[value])

Result would be shown as below.

 

Best Regards,

Jay

Community Support Team _ Jay Wang

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

[Anonymous]

@v-jayw-msft  Thanks for the detailed reply and sharing the visuals. The MINX measure solution works.

[mwegener · Accepted solution]

Hi @Anonymous ,

I think you can use MINX to create a measure that gives you the lowest percentage from Level 3 (City).

This Measure you can use for your conditional formatting.

https://docs.microsoft.com/en-us/dax/minx-function-dax

Regards,

Marcus

Dortmund - Germany
If I answered your question, please mark my post as solution, this will also help others.
Please give Kudos for support.

[Anonymous]

@mwegener - Thanks for the reply. The MINX measure works