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

     

     

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

        Hi Jihwan_Kim 
        Appreciating your support! I did't know how to attach the pbix here, so please find it with the following google drive link. 
        instead of formatting the font color, it would be great if we do it as background color instead. 

        if today value is bigger than previous value, then green background, and if small then red background. 

        Thank you very much once again!

        https://drive.google.com/file/d/1cPqkMiIHoidA6mUu9KRFuPte0zGRDNWx/view?usp=sharing