topic Row level Conditional formating for a group of rows in DAX Commands and Tips

Row level Conditional formating for a group of rows

Jagan_MFilterIT — Wed, 16 Mar 2022 11:51:25 GMT

Hi,

I have a requirement where i am requierd to show the minimum and maximum values of a group of rows. In the following example, we have a matrix visual which shows the price a product is sold by different sellers. The Matrix hirearchy has the following components; Product Description -> Platform -> Seller. Requirement is to show the lowest and highest price for a product per platform. Also the dataset has dates for the last six months, but the conditional formating takes into account only the last 7 days which is shown.

I tried the following measure for conditional formatting, but it doesnt work. Request your help on this!

MaxMin =

var asp = SELECTEDVALUE('0. ITC_KPI'[ASP])

var maxasp = CALCULATE(MAX('0. ITC_KPI'[ASP]),ALLSELECTED('0. ITC_KPI'[inserted_date]))

var minasp = CALCULATE(Min('0. ITC_KPI'[ASP]),ALLSELECTED('0. ITC_KPI'[inserted_date]))

var result =

SWITCH(

TRUE(),

asp=maxasp,"Red",

asp=minasp,"Green"

)

return result

Re: Row level Conditional formating for a group of rows

amitchandak — Wed, 16 Mar 2022 12:38:33 GMT

Jagan_MFilterIT , try like

MaxMin =
var asp = SELECTEDVALUE('0. ITC_KPI'[ASP])
var maxasp = CALCULATE(MAXX(values('0. ITC_KPI'[inserted_date]) , calculate(sum('0. ITC_KPI'[ASP]))),ALLSELECTED('0. ITC_KPI'[inserted_date]))
var minasp = CALCULATE(MinX(values('0. ITC_KPI'[inserted_date]) , calculate(sum('0. ITC_KPI'[ASP]))),ALLSELECTED('0. ITC_KPI'[inserted_date]))

var result =
SWITCH(
TRUE(),
asp=maxasp,"Red",
asp=minasp,"Green"
)

return result

If this does not help
Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

Re: Row level Conditional formating for a group of rows

Jagan_MFilterIT — Wed, 16 Mar 2022 13:17:40 GMT

Hi Amit,

Thanks a ton for replying. I tried the above and its actually highlighting all the rows as red,

Whereas, it should highlight red and green as below:

It also works differently if i remove the last 7 days filter on the visual..

Re: Row level Conditional formating for a group of rows

Jagan_MFilterIT — Wed, 16 Mar 2022 13:20:21 GMT

Bascially this logic needs to find min max of price by product by platform for a given date range(last days) amitchandak. I will try to share some sample data for reference

Re: Row level Conditional formating for a group of rows

Jagan_MFilterIT — Wed, 16 Mar 2022 14:10:08 GMT

Sir, I am also attaching the sample file : https://docs.google.com/spreadsheets/d/1WmDW7PGzAadGMN4qW-pdwKQmandg3n9-/edit?usp=sharing&ouid=105936777732667101640&rtpof=true&sd=true

Re: Row level Conditional formating for a group of rows

Anonymous — Thu, 24 Mar 2022 07:17:34 GMT

Hi Jagan_MFilterIT ,

Please refer to my pbix file to see if it helps you. Since I don't have the last 7 days of data, I chose the last 23 days of data.

Create a measure.

Measure = VAR date_ = MAX ( '0. ITC_KPI'[asp] ) VAR max_date = CALCULATE ( MAX ( '0. ITC_KPI'[asp] ), FILTER ( ALL ( '0. ITC_KPI' ), '0. ITC_KPI'[customer] = MAX ( '0. ITC_KPI'[customer] ) ) ) VAR min_date = CALCULATE ( MIN ( '0. ITC_KPI'[asp] ), FILTER ( ALL ( '0. ITC_KPI' ), '0. ITC_KPI'[customer] = MAX ( '0. ITC_KPI'[customer] ) ) ) VAR result = SWITCH ( TRUE (), date_ = max_date, 1, date_ = min_date, 0 ) RETURN IF ( MAX ( '0. ITC_KPI'[inserted_date] ) >= TODAY () - 23, result, BLANK () )

Then Create a measure again.

Measure 2= VAR date_ = MAX ( '0. ITC_KPI'[asp] ) VAR max_date = CALCULATE ( MAX ( '0. ITC_KPI'[asp] ), FILTER ( ALL ( '0. ITC_KPI' ), '0. ITC_KPI'[customer] = MAX ( '0. ITC_KPI'[customer] ) ) ) VAR min_date = CALCULATE ( MIN ( '0. ITC_KPI'[asp] ), FILTER ( ALL ( '0. ITC_KPI' ), '0. ITC_KPI'[customer] = MAX ( '0. ITC_KPI'[customer] ) ) ) VAR result = SWITCH ( TRUE (), date_ = max_date, "Red", date_ = min_date, "Green" ) RETURN IF ( MAX ( '0. ITC_KPI'[inserted_date] ) >= TODAY () - 23, result, BLANK () )

Then format the Measure2, select Format>>Conditional formatting>>Advanced controls.

Then custom the rules please.

If I have misunderstood your meaning, please provide your desired output with more details.

Best Regards

Community Support Team _ Polly

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