Forum Discussion

abukapsoun's avatar
abukapsoun
Post Patron
2 years ago
Solved

Formatting a matrix

Hello,


Need your kind support in the following scenario

I have a matrix view with the following:

Rows:

Customer

Project

 

Columns:

Quarter

Date

 

Value:
Sum

 

in Column Date, there is only 2 values (Yesterday, Today)

 

The view would give me something like below. I want to apply conditional formatting on "today values" which are 17/2/2024 in below table. If value bigger than yesterday, then font color of today value turns green. How can I do that?

 

Thank you

 

Need Help
  • Jihwan_Kim's avatar
    Jihwan_Kim
    2 years ago

    Hi,

    I tried not to alter the relationship or create new dimension tables in your semantic model. I suggest to try to create relationships with having new dimension tables.
    But for now, please check the below picture and the attached pbix file whether it suits your requirement.

     

     

  • Jihwan_Kim's avatar
    Jihwan_Kim
    2 years ago

    Hi, Please check the picture down below.

    Thank you.

     

     

10 Replies

  • Jihwan_Kim's avatar
    Jihwan_Kim
    Super User
    2 years ago

    Hi,

    I am not sure how your semantic model looks like, but I tried to create a sample pbix file like below.

    Please check the below picture and the attached pbix file.

     

     

     

    Font color condition: =
VAR _currentrev = [Revenue total:]
VAR _prevrev =
    CALCULATE (
        [Revenue total:],
        OFFSET (
            -1,
            FILTER ( ALL ( 'Calendar' ), [Revenue total:] <> BLANK () ),
            ORDERBY ( 'Calendar'[Date], ASC ),
            ,
            PARTITIONBY ( 'Calendar'[Year-Quarter] )
        )
    )
RETURN
    IF ( NOT ISBLANK ( _prevrev ) && _currentrev > _prevrev, "Green" )

     

     

    • abukapsoun's avatar
      abukapsoun
      Post Patron
      2 years ago

      Thank you very much for your swift reply, when I tried to create the font color condition measure, i got the following error: OFFSET 's relation parameter may have duplicate rows. This is not allowed. 
      Not sure how to proceed 😞

      • Jihwan_Kim's avatar
        Jihwan_Kim
        Super User
        2 years ago

        Hi, please share your sample pbix file, and then I can try to look into it. The above error may be solved by using MATCHBY function in OFFSET DAX function.

         

        OFFSET function (DAX) - DAX | Microsoft Learn

         

        But, please share your sample pbix file, and then we can try to find a way to solve the issue.

         

        Thanks.

  • abukapsoun's avatar
    abukapsoun
    Post Patron
    2 years ago

    My table has some other columns as well, however I am not using them in this specific matrix. Could it be the reason?