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 t...
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!!
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