Forum Discussion

cottrera's avatar
cottrera
Post Prodigy
2 years ago
Solved

Conditional format high and low points with DAX

Hi I have a line chart that has been formatted so that the error bars are displaying the results.

I have also useda field parameter in the Y axis


I would like to markers on the visual toshow as green for the lowest point and red for the hihest point. This needs to work if I change the selection in the field parameter filter.
thank you
Richard


 

 

Need Help
  • shafiz_p's avatar
    shafiz_p
    2 years ago

    Hi, cottrera, Since no data provided, I have used adventure works data model to replicate the scenario. You can use the given min and max highlight measure according to your data.

    Max Highlight =
    VAR _Max_Value_Sales = CALCULATE(MAXX(VALUES('Date'[EnglishMonthName]), [Internet Net Sales]), ALLSELECTED('Internet Sales'))
    VAR _Max_Value_Cost = CALCULATE(MAXX(VALUES('Date'[EnglishMonthName]), [Product Cost]), ALLSELECTED('Internet Sales'))

    VAR _Only_Max_Sales = IF([Internet Net Sales] = _Max_Value_Sales, _Max_Value_Sales, BLANK())
    VAR _Only_Max_Cost = IF([Product Cost] = _Max_Value_Cost, _Max_Value_Cost, BLANK())

    VAR _SelectedValue = SELECTEDVALUE(Parameter[Parameter Order])

    RETURN
    SWITCH(
        TRUE(),
        _SelectedValue = 0, _Only_Max_Sales,
        _SelectedValue = 1, _Only_Max_Cost,
        _Only_Max_Sales
    )
     
    Min Highlight =
    VAR _Min_Value_Sales = CALCULATE(MINX(VALUES('Date'[EnglishMonthName]), [Internet Net Sales]), ALLSELECTED('Internet Sales'))
    VAR _Min_Value_Cost = CALCULATE(MINX(VALUES('Date'[EnglishMonthName]), [Product Cost]), ALLSELECTED('Internet Sales'))

    VAR _Only_Min_Sales = IF([Internet Net Sales] = _Min_Value_Sales, _Min_Value_Sales, BLANK())
    VAR _Only_Min_Cost = IF([Product Cost] = _Min_Value_Cost, _Min_Value_Cost, BLANK())

    VAR _SelectedValue = SELECTEDVALUE(Parameter[Parameter Order])

    RETURN
    SWITCH(
        TRUE(),
        _SelectedValue = 0, _Only_Min_Sales,
        _SelectedValue = 1, _Only_Min_Cost,
        _Only_Min_Sales
    )
    Place Field Parameter, Max and Min Hightlight measure in Y axis. Change color of the individual line in Line option. This is how it's look like :

     


     


    Hope this Helps!!

    If this solved your problem, please mark it as a solution!!

4 Replies

  • shafiz_p's avatar
    shafiz_p
    Super User
    2 years ago

    Hi, cottrera, Since no data provided, I have used adventure works data model to replicate the scenario. You can use the given min and max highlight measure according to your data.

    Max Highlight =
    VAR _Max_Value_Sales = CALCULATE(MAXX(VALUES('Date'[EnglishMonthName]), [Internet Net Sales]), ALLSELECTED('Internet Sales'))
    VAR _Max_Value_Cost = CALCULATE(MAXX(VALUES('Date'[EnglishMonthName]), [Product Cost]), ALLSELECTED('Internet Sales'))

    VAR _Only_Max_Sales = IF([Internet Net Sales] = _Max_Value_Sales, _Max_Value_Sales, BLANK())
    VAR _Only_Max_Cost = IF([Product Cost] = _Max_Value_Cost, _Max_Value_Cost, BLANK())

    VAR _SelectedValue = SELECTEDVALUE(Parameter[Parameter Order])

    RETURN
    SWITCH(
        TRUE(),
        _SelectedValue = 0, _Only_Max_Sales,
        _SelectedValue = 1, _Only_Max_Cost,
        _Only_Max_Sales
    )
     
    Min Highlight =
    VAR _Min_Value_Sales = CALCULATE(MINX(VALUES('Date'[EnglishMonthName]), [Internet Net Sales]), ALLSELECTED('Internet Sales'))
    VAR _Min_Value_Cost = CALCULATE(MINX(VALUES('Date'[EnglishMonthName]), [Product Cost]), ALLSELECTED('Internet Sales'))

    VAR _Only_Min_Sales = IF([Internet Net Sales] = _Min_Value_Sales, _Min_Value_Sales, BLANK())
    VAR _Only_Min_Cost = IF([Product Cost] = _Min_Value_Cost, _Min_Value_Cost, BLANK())

    VAR _SelectedValue = SELECTEDVALUE(Parameter[Parameter Order])

    RETURN
    SWITCH(
        TRUE(),
        _SelectedValue = 0, _Only_Min_Sales,
        _SelectedValue = 1, _Only_Min_Cost,
        _Only_Min_Sales
    )
    Place Field Parameter, Max and Min Hightlight measure in Y axis. Change color of the individual line in Line option. This is how it's look like :

     


     


    Hope this Helps!!

    If this solved your problem, please mark it as a solution!!
    • cottrera's avatar
      cottrera
      Post Prodigy
      2 years ago

      Thank you for your quick reponse this solution worked for my error bars

  • bhanu_gautam's avatar
    bhanu_gautam
    Super User
    2 years ago

    cottrera , You can create one measure for Max and Min Value

     

    MinValue =
    CALCULATE(
    MIN(YourTable[YourField]),
    ALLSELECTED(YourTable)
    )

    MaxValue =
    CALCULATE(
    MAX(YourTable[YourField]),
    ALLSELECTED(YourTable)
    )

     

    One measure for Marker Color

    MarkerColor =
    SWITCH(
    TRUE(),
    SELECTEDVALUE(YourTable[YourField]) = [MinValue], "Green",
    SELECTEDVALUE(YourTable[YourField]) = [MaxValue], "Red",
    "DefaultColor" // Replace with the default color you want for other points
    )

     

    Click on the line chart to select it.
    Go to the "Format" pane.
    Expand the "Data colors" section.
    Click on the "fx" button next to the color option.
    In the "Based on field" dropdown, select the MarkerColor measure you created.

    • cottrera's avatar
      cottrera
      Post Prodigy
      2 years ago

      Hi thank you for responding so quickly unfortunatley as i was using error bards there was no visible format for the marker colours so I could not try you DAX