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

Row level Conditional formating for a group of rows

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. 

 

Jagan_MFilterIT_0-1647431188480.png

 

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

 

5 REPLIES 5
v-rongtiep-msft
Community Support
Community Support

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.

 

vpollymsft_0-1648106026128.png

Then custom the rules please.

vpollymsft_1-1648106181899.png

 

vpollymsft_2-1648106214573.png

 

 

 

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.

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

amitchandak
Super User
Super User

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

Hi Amit, 

 

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

Jagan_MFilterIT_0-1647436520316.png

 

Whereas, it should highlight red and green as below: 

 

Jagan_MFilterIT_1-1647436597950.png

 

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

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