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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Conditionally format YEARLY MAX and MIN values in line chart / column chart

Hello everyone, 

 

Please help to write a measure that calculates yearly MAX and yearly MIN values so that I can use this measure to conditionally format points on the line chart or columns on the bar chart. Here is a simple example done in Excel. I want to achieve the same result in Power BI. 

Example.jpg

DateValue
01.01.2017          0,33
01.02.2017          0,26
01.03.2017          0,04
01.04.2017          0,79
01.05.2017          0,65
01.06.2017          0,29
01.07.2017          0,71
01.08.2017          0,21
01.09.2017          0,12
01.10.2017          0,38
01.11.2017          0,69
01.12.2017          0,42
01.01.2018          0,95
01.02.2018          0,74
01.03.2018          0,05
01.04.2018          1,00
01.05.2018          0,41
01.06.2018          0,98
01.07.2018          0,04
01.08.2018          0,17
01.09.2018          0,11
01.10.2018          0,82
01.11.2018          0,84
01.12.2018          0,54
01.01.2019          0,71
01.02.2019          0,84
01.03.2019          0,58
01.04.2019          0,19
01.05.2019          0,50
01.06.2019          0,11
01.07.2019          0,89
01.08.2019          0,41
01.09.2019          0,51
01.10.2019          0,23
01.11.2019          0,74
01.12.2019          0,57

Thank you for attention to my post. 

5 REPLIES 5
Anonymous
Not applicable

Hello to whoever is looking through this thread. 

So far, I have only managed to highlight MIN and MAX for ENTIRE range of dates, however, this is not what I need. I still hope someone helps me out to write a measure to highlight yearly/annual MAX and MIN value. 

Ruslanakh_0-1613059003478.png

 

 

amitchandak
Super User
Super User

@Anonymous , Create a bar visual and use a measure like give below in data color (advance option) with field value option (conditional formatting)

 

measure =
var _min = minx(values(Date[Date]),[Value])
var _max = minx(values(Date[Date]),[Value])
return
Switch(true() ,
[Value] = _min , "red",
[Value] = _max , "green",
"blue"
)

 

And then convert to like with marker.

 

steps: https://radacad.com/dax-and-conditional-formatting-better-together-find-the-biggest-and-smallest-numbers-in-the-column
https://docs.microsoft.com/en-us/power-bi/desktop-conditional-table-formatting#color-by-color-values

Anonymous
Not applicable

Thanks for response, but it just highlighted everythin in red. Any other ideas? 

Ruslanakh_0-1612955085398.png

 

@Anonymous , put a calculate on top of sum in first two var and try

measure =
var _min = minx(values(Date[Date]),calculate(sum(Table[Value])))
var _max = minx(values(Date[Date]),calculate(sum(Table[Value])))
return
Switch(true() ,
[Value] = _min , "red",
[Value] = _max , "green",
"blue"
)

 

Anonymous
Not applicable

@amitchandak , unfortunately, it still shows the same result. Looks like this measure does not go beyond MIN. Any other ideas? Thank you for help. 

Ruslanakh_0-1613015859592.png

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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

Top Solution Authors