Forum Discussion

cottrera's avatar
cottrera
Icon for Post Prodigy rankPost 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!!
shafiz_p's avatar
shafiz_p
Icon for Super User rankSuper 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
Icon for Post Prodigy rankPost Prodigy
2 years ago

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