topic Re: Conditional format high and low points with DAX in DAX Commands and Tips

Conditional format high and low points with DAX

cottrera — Wed, 17 Jul 2024 05:50:14 GMT

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

Re: Conditional format high and low points with DAX

bhanu_gautam — Wed, 17 Jul 2024 06:09:51 GMT

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.

Re: Conditional format high and low points with DAX

shafiz_p — Wed, 17 Jul 2024 10:07:23 GMT

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

Re: Conditional format high and low points with DAX

cottrera — Thu, 18 Jul 2024 08:30:45 GMT

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

Re: Conditional format high and low points with DAX

cottrera — Thu, 18 Jul 2024 08:31:53 GMT

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