topic Re: Max and Min Price for Conditional Formating in DAX Commands and Tips

Max and Min Price for Conditional Formating

Elcin_7 — Fri, 17 May 2024 07:51:28 GMT

Hi all,

I have 2 tables Calendar and Raw_Data.

I need min and max price for each month in my table with conditional format. With the dax formula below. I am getting the result for whole table. I need to highligt Max and min by month . As an example in my table below. 19.46 needs to be green as well because it is min price for Apr. Can you help me please?

Please see the dax formula

Min or Max =

VAR ValuesDisplayed =

CALCULATETABLE(

ADDCOLUMNS(

SUMMARIZE(

Raw_Data,

Raw_Data[Part_Number],

Raw_Data[merchantName],

Raw_Data[merchantLink],

'Calendar'[Month Name Short]

"TEST", [Price2]

ALLSELECTED()

)

VAR MinPrice = MINX(ValuesDisplayed, [TEST])

VAR MaxPrice = MAXX(ValuesDisplayed, [TEST])

VAR CurrentPrice = [Price2]

VAR Result =

SWITCH(

TRUE(),

CurrentPrice = MinPrice, 1,

CurrentPrice = MaxPrice, 2,

)

RETURN

Result

I can use table or matrix

Re: Max and Min Price for Conditional Formating

xifeng_L — Fri, 17 May 2024 08:47:46 GMT

Hi Elcin_7 ,

Just remove the month from the SUMMARIZE function.

Min or Max = VAR ValuesDisplayed = CALCULATETABLE( ADDCOLUMNS( SUMMARIZE( Raw_Data, Raw_Data[Part_Number], Raw_Data[merchantName], Raw_Data[merchantLink] ), "TEST", [Price2] ), ALLSELECTED() ) VAR MinPrice = MINX(ValuesDisplayed, [TEST]) VAR MaxPrice = MAXX(ValuesDisplayed, [TEST]) VAR CurrentPrice = [Price2] VAR Result = SWITCH( TRUE(), CurrentPrice = MinPrice, 1, CurrentPrice = MaxPrice, 2, 0 ) RETURN Result

Did I answer your question? If yes, pls mark my post as a solution and appreciate your Kudos !

Thank you~

Re: Max and Min Price for Conditional Formating

Elcin_7 — Fri, 17 May 2024 08:55:48 GMT

No 😞 still, I am getting the same result.

Re: Max and Min Price for Conditional Formating

xifeng_L — Fri, 17 May 2024 09:00:44 GMT

Try removing merchantLink and Part_Number again.

Min or Max = VAR ValuesDisplayed = CALCULATETABLE( ADDCOLUMNS( SUMMARIZE( Raw_Data, Raw_Data[merchantName] ), "TEST", [Price2] ), ALLSELECTED() ) VAR MinPrice = MINX(ValuesDisplayed, [TEST]) VAR MaxPrice = MAXX(ValuesDisplayed, [TEST]) VAR CurrentPrice = [Price2] VAR Result = SWITCH( TRUE(), CurrentPrice = MinPrice, 1, CurrentPrice = MaxPrice, 2, 0 ) RETURN Result

If that doesn't work, you can provide some example data or pbix files so that I can better help you pinpoint the problem.

Re: Max and Min Price for Conditional Formating

Anonymous — Mon, 20 May 2024 07:56:31 GMT

Hi Elcin_7 ,

You can try formula like below and use it in conditional formatting:

MEASURE = VAR max_ = MAXX ( FILTER ( ALL ( Raw_Data ), Raw_Data[Part_Number] = MAX ( Raw_Data[Part_Number] ) && Raw_Data[Month Name Short] = MAX ( Raw_Data[Month Name Short] ) ), [Price2] ) VAR min_ = MINX ( FILTER ( ALL ( Raw_Data ), Raw_Data[Part_Number] = MAX ( Raw_Data[Part_Number] ) && Raw_Data[Month Name Short] = MAX ( Raw_Data[Month Name Short] ) ), [Price2] ) RETURN IF ( MAX ( Raw_Data[Price2] ) = max_, "red", IF ( MAX ( Raw_Data[Price2] ) = min_, "green" ) )

Best Regards,
Adamk Kong

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Re: Max and Min Price for Conditional Formating

Elcin_7 — Mon, 20 May 2024 08:08:44 GMT

This logic worked. Thank you.

Re: Max and Min Price for Conditional Formating

Anonymous — Mon, 20 May 2024 08:27:45 GMT

Hi Elcin_7 ,

Thanks for your feedback.

Best Regards,
Adamk Kong