Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

Reply
cottrera
Post Prodigy
Post Prodigy

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.

cottrera_0-1721195289716.png

I have also useda field parameter in the Y axis

cottrera_1-1721195327280.png
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


 

 

1 ACCEPTED SOLUTION
shafiz_p
Super User
Super User

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 :

Screenshot 2024-07-17 155349.png

 


 


Hope this Helps!!

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

View solution in original post

4 REPLIES 4
shafiz_p
Super User
Super User

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 :

Screenshot 2024-07-17 155349.png

 


 


Hope this Helps!!

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

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

bhanu_gautam
Super User
Super User

@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.




Did I answer your question? Mark my post as a solution! And Kudos are appreciated

Proud to be a Super User!




LinkedIn






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 

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.

Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Jan NL Carousel

Fabric Community Update - January 2025

Find out what's new and trending in the Fabric community.

Top Solution Authors