Highlighting max & min value in column chart for date/date

nikahafiz · July 27, 2022, 2:04am

hi,
pursuant to a previous posting, i have a similar issue but on the date/day level.

kindly ref to the sample file here.

while i’m able to do the conditional formatting to highlight the min/max values for each year in column charts for quarter & month (as shown in the sample file), i’m unable to do the same on the date/day-basis.

i managed to get the desired result for the month column chart using following dax formula

VAR MaxValue =
MAXX( ALLSELECTED ( ‘Calendar’[Month], ‘Calendar’[MonthNo] ),
[Del-DpAr-Dur] )

VAR MinValue =
MINX( ALLSELECTED ( ‘Calendar’[Month], ‘Calendar’[MonthNo] ),
[Del-DpAr-Dur] )

VAR Color =
IF( [Del-DpAr-Dur] = MaxValue, “#F9D987”,
IF( [Del-DpAr-Dur] = MinValue, “#AADBD6”,
“#D3D9B4”) )
RETURN Color

i’d appreciate any help to b able to hilite the min/max values for the day/dated for each year in a column chart.

many tks & krgds,

-nik

Harsh · July 27, 2022, 4:14am

Hello @nikahafiz,

Thank You for posting your query onto the Forum.

Do you want to highlight the max and min day for each year? Like this -

In that case, below is the measure alongwith the screenshot of the final results provided for the reference -

Del-DpAr-Dur-MxMn-D-CF - Harsh =
VAR _Max_Value =
CALCULATE(
    MAXX(
        ALLSELECTED( 'Calendar' ) ,
            [Del-DpAr-Dur] ) ,
    ALLEXCEPT( 'Calendar' , 'Calendar'[Year] ) ,
    VALUES( 'Calendar'[Year] ) )

VAR _Min_Value =
CALCULATE(
    MINX(
        ALLSELECTED ( 'Calendar' ) ,
            [Del-DpAr-Dur] ),
    ALLEXCEPT( 'Calendar' , 'Calendar'[Year] ) ,
    VALUES( 'Calendar'[Year] ) )

VAR _Color =
IF( [Del-DpAr-Dur] = _Max_Value , "#F9D987" ,
IF( [Del-DpAr-Dur] = _Min_Value , "#AADBD6" ,
    "#D3D9B4") )
   
RETURN
_Color

The same will be applicable for the Min Values as well.

I’m also attaching the working of the PBIX file for the reference purposes.

Hoping you find this useful and meets your requirements that you’ve been looking for.

Thanks and Warm Regards,
Harsh

demo-dataset2-JFK - Harsh.pbix (13.4 MB)

nikahafiz · July 27, 2022, 9:54am

Hi @Harsh,

Many thanks for your quick & courteous response.

I’m sorry for my late reply too as I took some time to check the DAX formula which I have modified for other, similar conditional formatting at the date/day level in the application I’m trying to develop. So far, all seems to be working as I wanted it to be.

Meanwhile, as I’m rather new in DAX (particularly in the different context DAX implementations), can you kindly explain on how does your DAX formula work?

Many thanks again & kind regards,
-Nik

Harsh · July 27, 2022, 11:36am

Hello @nikahafiz,

You’re Welcome!!!

I’m glad that I was able to assist you.

Here’s the explanation about why the old measure didn’t work and how it was corrected to achieve the results -

In the old measure, the function that was referenced was “ALLSELECTED()”. Now, this function works in similar manner in comparison to the “ALL()” function when nothing is selected on the report. So since there’re no slicers onto the report, the results which will be evaluated in this scenario is as good as “ALL()” function being referenced inside the measure. And therefore, it evalauted the MAX and MIN values for the entire time period i.e., for the both years 2015 and 2016. Below is the screenshot provided for the reference -

As you can observe in the above screenshot, eventhough the years change but the MAX values remain same for both the years. And therefore, since we want the MAX and MIN values for each year and not for the entire time frame. We made a usgae of “ALLEXCEPT() and VALUES()” function.

So when “ALLEXCEPT()” function is used, it removed the context at the Yearly level giving us the exact same results like you were getting it previously. Below is the screenshot provided for the reference -

But now, when we reference “VALUES()” function inside it, we are bringing in back the context which was previously removed from all the Years. And now, we’ll get two different MAX values for two different years i.e., one for 2015 and one for 2016. Below is the screenshot provided for the reference -

I’m also providing few of the links below pertaining to this topic for the reference purposes.

Hoping you find this explanation helpful and strengthens your understanding of this concept.

Thanks and Warm Regards,
Harsh

nikahafiz · July 28, 2022, 1:39am

Many thanks again, @Harsh.

I’ll go through again those tutorials.

Much appreciated & kind regards,
-Nik