Conditional Formatting not working as required on Drill Through bar Chart

[Anonymous]

Hi All,

I have been trying to resolve a conditional fomatting issue but unsuccessfully. I have a bar chart with two levels, top level is German States, the next level is Districts (each state consists of one or more districts). Chart shows number of road accidents per area and I want to highlight the areas with the highest & lowest accident numbers.
At the top level the chart is highlighting Berlin (State) which nationally is the District(Berlin) with the highest number of accidents but it is not the State with the highest number. This is is Nordrhein-Westfallen (NRW).
If I drill through a State to the next level - District, the formatting works fine at that level.
If I remove 'District level' - District[District] from the formatting measure [MaxNrAcc], the formatting works fine at State level correctly formatting the State NRW. The formatting code I used is as follows:

MaxNrAcc =

// [Nr_Accidents] is Countrows(Road_Accidents_DE) - each row is one accident

    Var ValuesDisplayed =

        CALCULATETABLE(    

                ADDCOLUMNS(

                    SUMMARIZE(Road_Accidents_DE,District[State],District[District]),

                    "@Amt", [Nr_Accidents]

                ),

                ALLSELECTED()

            )

    Var MinVal = MINX (ValuesDisplayed,[@Amt])

    Var MaxVal = Maxx (ValuesDisplayed,[@Amt])

    Var CurrentVal = [Nr_Accidents]

    Var Result =

        SWITCH(

            TRUE(),

            CurrentVal = MinVal, 1,

            CurrentVal = MaxVal, 2

        )

    RETURN

        Result

I've tried several variations of this but cannot figure out where I'm going wrong whether it's my measure or how I've configured the Data Model. Any advice greatly appreciated.

EDIT - link to pbix https://drive.google.com/file/d/1Ss33O6Gk1VqFGRITatXSvzs6sn22LWnL/view?usp=sharing 

NRW is highest not Berlin - & no lowest formatting

If I drill through the State Sarland for example everything formats as expected - highest & lowest areas formatted

If I remove Districts from my formatting measure, States format as required but I then will have no formatting at District Level

This is my Data Model

Any advice greatly appreciated.

[Anonymous]

Hi  @Anonymous ,

Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data. We can better understand the problem and help you

Best Regards,

Liu Yang

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

[Anonymous]

Hi Liu Yang,

The data isn't sensitive as this is public information. Here is a link to the pbix https://drive.google.com/file/d/1Ss33O6Gk1VqFGRITatXSvzs6sn22LWnL/view?usp=sharing 

Kind Regrads

Martin