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 ...
Need Help
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
    )