Forum Discussion

bhuprakashs's avatar
bhuprakashs
Helper I
6 months ago

Conditional Formatting the row based on Maximum Value in Latest Month

Hi Team,

I have a Power BI matrix with:

  • Rows → Market Area
  • Columns → Month (e.g. October 2025, November 2025, December 2025, January 2026)
  • Values → a cumulative score measure

What I want to achieve

I want to apply conditional formatting (background color) to the matrix with the following logic:

  1. Identify the latest month dynamically based on the current report selection

    • Example: if the latest visible month is January 2026

  2. Within that latest month only, find:

    • The maximum score value
    • The Market Area that has this maximum value
    • Example: in January 2026, West has the highest value (52.3)

       

  3. Once the winning Market Area is identified, I want to:

    • Highlight the entire row for that Market Area
    • This means all month columns for that Market Area (Oct, Nov, Dec, Jan) should be colored
    • Not just the latest month cell

      Please help me how to do this. Thank you so much!


Need Help

9 Replies

  • MFelix's avatar
    MFelix
    Super User
    6 months ago

    Hi bhuprakashs ,

     

    Add the following measure to your code:

    Format =
VAR _MaxPeriod =
    MAXX ( ALLSELECTED ( 'Table'[Date] ), 'Table'[Date] )
VAR _MaxValue =
    TOPN (
        1,
        SUMMARIZE (
            CALCULATETABLE (
                SELECTCOLUMNS ( 'Table', 'Table'[Market], 'Table'[Date] ),
                'Table'[Date] = _MaxPeriod,
                REMOVEFILTERS ( 'Table'[Market] )
            ),
            'Table'[Date],
            'Table'[Market],
            "C", [Score Value]
        ),
        [C]
    )
RETURN
    IF (
        SELECTEDVALUE ( 'Table'[Market] ) = MAXX ( _MaxValue, 'Table'[Market] ),
        1
    )

    Be aware that you must change the Table and corresponding columns to match your model and I assume you are using a measure for the score then just add the condittional formatting based on this measure:

     

    • bhuprakashs's avatar
      bhuprakashs
      Helper I
      6 months ago

      Hi MFelix  Thanks for your help.
      In below line , seems Market and Date are coming from same table. But I have these 2 different Dim tables.

      Market is coming from Locations Table and Dates ( which is Month Year in my visual) are coming from Dates table.

      Could you please help me how to calculate this since it's not working in my case. Thanks

      SELECTCOLUMNS ( 'Table', 'Table'[Market], 'Table'[Date] ),

       

      • MFelix's avatar
        MFelix
        Super User
        6 months ago

        Hi bhuprakashs ,

         

        Since they are coming from dimension tables try the following code:

        SELECTCOLUMNS ( 'Table', "Market", RELATED('Market Table'[Market]),"Date", RELATED( 'Date Table'[Date] )),

         

        However this also need some adjustments in the rest of the calculation.

         

        Please see the full formula with the adjustments based on a model I have with two dimensions one for calendar another for Products.

        Format = 
VAR _MaxPeriod =
    MAXX ( ALLSELECTED ( 'Calendar'[Year] ), 'Calendar'[Year] )
VAR _MaxValue =
    TOPN (
        1,
        SUMMARIZE (
            CALCULATETABLE (
                SELECTCOLUMNS ( 'Sales Order Detail', "@Market", RELATED(Products[Class]),"@Date", RELATED( 'Calendar'[Year] )),
                'Calendar'[Year] = _MaxPeriod,
                REMOVEFILTERS ( Products[Class] )
            ),
            [@Date],
            [@Market],
            "C", [Total Sales]
        ),
        [C]
    )
RETURN
    IF (
        SELECTEDVALUE ( Products[Class] ) = MAXX ( _MaxValue, [@Market] ),
        1
    )
      • MFelix's avatar
        MFelix
        Super User
        5 months ago

        Hi bhuprakashs ,

         

        Can you please share a mockup data or sample of your PBIX file. You can use a onedrive, google drive, we transfer or similar link to upload your files.

        If the information is sensitive please share it trough private message.

  • v-veshwara-msft's avatar
    v-veshwara-msft
    Community Support
    5 months ago

    Hi bhuprakashs ,
    Thanks for reaching out to Microsoft Fabric Community.

     

    Just checking in to see if you query is resolved and if any responses were helpful.
    Otherwise, feel free to reach out for further assistance.

    Thank you.

  • v-veshwara-msft's avatar
    v-veshwara-msft
    Community Support
    5 months ago

    Hi bhuprakashs ,

    Just wanted to check in regarding your query. If you need further assistance, please share sample data or sample Pbix file as suggested by MFelix as that would help assist better.

     

    Thank you.