Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago

Conditional Formatting not working as required on Drill Through bar Chart

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.

Need Help

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    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