Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

DAX formula for conditional formatting column chart

I hope someone can help, cause sometimes propagation and filter context is difficult.

Regards
Ron 

Need Help
  • Padycosmos's avatar
    Padycosmos
    3 years ago

    Here's the measure that works:

    Colour3 =
    var _differ = MAXX(ALLSELECTED('KPI Dimension'),1-[KPI perc])
    var _target = [Check Against Target]
    RETURN
    SWITCH(
        TRUE(),
        _target >=1, "Green",
        _target < 1 && _target >=_differ, "Purple",
         _target < _differ , "Red"
       
        )

13 Replies

  • Padycosmos's avatar
    Padycosmos
    Solution Sage
    3 years ago

    In Power BI, though the display format is %, the numbers are stored as decimals. Please make the following changes and try again

    var _differ = 1- [KPI perc]

    Replace both the 100s in the SWITCH() to 1 

     

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Hi Padycosmos thanx for you answer
    I changed the measure as follows (by the way I replaced Amber by Purple, because for Amber it returs Blue, which seems odd)

    Color =
    var _differ = 1 - [KPI perc]
    RETURN
    SWITCH(
        TRUE(),
        [Check Against Target] >= 1, "Green",
        [Check Against Target] >= _differ && [Check Against Target] < 1, "Purple",
        [Check Against Target] <= _differ, "Red"
        )
     
    Now it returns this


    So it is already better but 202202 should be Red. 

    The funny thing is, when clicking 202202 in the column chart and putting the measure Color in the table, it shows Red
    That's really odd


    • Padycosmos's avatar
      Padycosmos
      Solution Sage
      3 years ago

      You have >=_differ in "Purple" and  <= _differ  in "Red", you need to remove one of the = . Then it should work

      • Anonymous's avatar
        Anonymous
        Not applicable
        3 years ago

        Hi Padycosmos 

        unfortunately no change
        I changed 

            [Check Against Target] <= _differ, "Red"
        into
            [Check Against Target] < _differ, "Red"
         
        The tabel shows Red, te column chart Purple for 202202