Forum Discussion

bhuprakashs's avatar
bhuprakashs
Icon for Helper I rankHelper 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
Icon for Super User rankSuper 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
    Icon for Helper I rankHelper 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
      Icon for Super User rankSuper 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
      Icon for Super User rankSuper User
      6 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.

      • bhuprakashs's avatar
        bhuprakashs
        Icon for Helper I rankHelper I
        6 months ago

        Hi MFelix I have restrictions on my laptop to share anything. will try to create a dummy data and try to share with you.
        My base measure is created from 2-3 different tables and on top of that some business logics are applied, which I am using here. May be due to that , it's not working.