Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
abukapsoun
Post Patron
Post Patron

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

abukapsoun_0-1708260832751.png

 

2 ACCEPTED SOLUTIONS

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_0-1708318182960.png

 

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.


Visit my LinkedIn page by clicking here.


Schedule a meeting with me to discuss further by clicking here.

View solution in original post

Hi, Please check the picture down below.

Thank you.

 

Jihwan_Kim_0-1708364513228.png

 

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.


Visit my LinkedIn page by clicking here.


Schedule a meeting with me to discuss further by clicking here.

View solution in original post

10 REPLIES 10
abukapsoun
Post Patron
Post Patron

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

Jihwan_Kim
Super User
Super User

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.

 

Jihwan_Kim_0-1708262642160.png

 

 

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" )

 

 

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.


Visit my LinkedIn page by clicking here.


Schedule a meeting with me to discuss further by clicking here.

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 😞

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.

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.


Visit my LinkedIn page by clicking here.


Schedule a meeting with me to discuss further by clicking here.

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

 

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_0-1708318182960.png

 

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.


Visit my LinkedIn page by clicking here.


Schedule a meeting with me to discuss further by clicking here.

I have added the other columns into PARTITIONBY and MATCHBY and now it is working like charm!! Thank you! 
One last thing, how to apply the same on the Total row at the buttom of the matrix?

Hi, Please check the picture down below.

Thank you.

 

Jihwan_Kim_0-1708364513228.png

 

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.


Visit my LinkedIn page by clicking here.


Schedule a meeting with me to discuss further by clicking here.

Hi @Jihwan_Kim 

I still need your help please. I found out that in case some rows are available in earlier date, and not in later date or vice versa. formatting is happening randomly. I tried as much as possible to crack but in vain 😞

If you don't mind I am sharing my full date, however I have renamed all field for data sensitivity matters. 

https://drive.google.com/file/d/1kczK4cY1kw4mVrTXzkTie74HHhE-dLKR/view?usp=sharing

 

for example in 2024Q2 row QOpg5ckTLFycbVD7kRdU0r2MGd, the later date is highlighted in green, while both have same values. 

😞

Thank you once again for the effort! The formula was accepted by DAX. But when I am putting it as a conditional formatting as suggest, I got the below error:

 

Despite of MatchBy columns being specified, duplicated rows are encountered in OFFSET's relation parameter. This is not allowed

Is there a way maybe we can use other than OFFSET?

 

Thank you!

Helpful resources

Announcements
Sept PBI Carousel

Power BI Monthly Update - September 2024

Check out the September 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

Sept NL Carousel

Fabric Community Update - September 2024

Find out what's new and trending in the Fabric Community.