Conditional format in Matrix table

Hi, Everyone,

I have a matrix table and I’d like to format the data in Matrix based on the data in two the segments. For example, channel 1 in week#1 is 87.5% and channel 2 is 29.42%. Is there a way we can format data in Channel 1 compared to data in Channel 2? such as adding an arrow or adding background color to data in each column for every product?

image

If you know this has been discussed in the form in the past, please share me the link to the topic.

Thanks!

Mike

Inventory_data_eNDA.pbix (116.1 KB)

Hi @Mike,

You will have to ‘translate’ the condition to DAX, so you can assign it a value for conditional formatting. See this topic to get an impression of how that could work.

If you need a pointer for the DAX logic… the important thing is to remove the Channel from the filter context in order to perform this comparison. See Accelerator W3 to learn about that.

At this time you only have 2 channels in your model - is that an accurate representation? If not, think about adding a Channel DIM table with an Index to make it easier, for example - the table below - will allow you to compare a next with a previous channel.

Channel ID Channel
1 Channel1
2 Channel2

I hope this is helpful

1 Like

Hi, @Melissa thanks for your guidance and it is very helpful.

The formula to create DAX based the data in table column works great, what I am trying to achieve here is to conditional format based on the data comparison of a measure. As you can see below, the percentage in the table is a measure and I’d like to compare the data at product level (right table) and aggregated level (left table). Do you think it is doable to format Channel #1 value based on if the data is each column is greater or less than the value in Channel#2?

Thanks for your time! Greatly appreciated!

Mike

@Mike ,

It’s absolutely doable. I recently worked through a similar case, where we were conditionally formatting based on comparison of current value with previous week’s value.

Conditional formatting in matrix comparing value with last column with data

I hope this is helpful.

  • Brian

@BrianJ , Hi, Brian, thanks for sharing the post. I will take a deep look at your solution. Very excited to know it is doable. You guys are genius!

Thanks again!

Mike

2 Likes

@BrianJ , I am reading your post and your solution, OMG! my mind is blown away. Virtual table seems a key in PowerBI for a lot of complicated problems. I will try to see if I can understand the logic and of course the syntax of DAX formula. Wish I could borrow your brain!

Thanks!

Mike

1 Like

@Mike ,

Virtual tables are the key to doing almost anything advanced in DAX. Once you’ve really grasped filter context, row context and variables (our primary focus in the Power BI Accelerator course the first 4 weeks), virtual tables are the final key thing to tackle. It’s actually my favorite part of DAX, and TE3 makes it MUCH easier to learn than prior, since it integrates DAX queries into the editor, so you can visualize all the virtual tables within your measures.

In addition to @AntrikshSharma’s fantastic course on TE3, check out this video which demonstrates how you can use it to take apart and understand complex measures like the conditional formatting one I provided above.

  • Brian
3 Likes

Hello @Mike, good to see that you are having progress with your inquiry. Did the response from @BrianJ help you solve your inquiry? If not, how far did you get and what kind of help you need further? If yes, kindly mark as solution the answer that solved your query.

1 Like

@Mike ,

Did you get it figured out? I was planning to jump into this later this afternoon after finishing some work on Accelerator, but saw you’d withdrawn the question.

  • Brian

@BrianJ Hi, Brian, It took me hours to go through your video to understand how the virtual table works and figure out the purpose of the virtual table. It was a very rewarding learning journey, and I finally figured out what I needed to change the logic in the virtual table to fit my data model. Based on what I learned from this example, the metrics in virtual table is not the key, the reason to have the virtual table is to keep the context of the data. Please let me know if this makes sense.

ST_Channel2 =
/*
var vTable =
FILTER(ADDCOLUMNS(
SUMMARIZE(ALl(Fact_Tbl),
Fact_Tbl[Channel],
Fact_Tbl[NumOfWeek]
),
@percentage”, DIVIDE([Opening Inventory]-[Inventory], [Opening Inventory], 0)
),
[@percentage] <> Blank()
)
*/

var result =
CALCULATE([Inventory Rate],
FILTER(
    ALLEXCEPT(Fact_Tbl, Fact_Tbl[NumOfWeek], Fact_Tbl[ProductID]),
    Fact_Tbl[Channel] ="Channel2"
)
)
return result

Inventory_data_eNDA.pbix (102.2 KB)

It is working greatly right now and I can format the Channel#1 data based on the comparison to data in Channel#2. Even though it took me the whole long weekend, but it is so worth it.

I believe in my case, I don’t need the virtual table.

Thank you very much for guiding me through!

Mike

2 Likes

@Mike ,

Great work! There’s really no substitute for grinding your way through a complex problem like this one, in terms of the amount of learning done.

And yes, you are absolutely spot on that in ADDCOLUMNS/SUMMARIZE construct, the SUMMARIZE table is what creates the proper context for the new columns calculated in the ADDCOLUMNS statement.

Your virtual table is very well constructed, in that it provides the context via SUMMARIZE, calculates the key percentage via ADDCOLUMNS (even using the proper virtual column notation. :+1: ) and then filters based on that virtual column. If you full grasp all of this, you are a long way down the road already to mastering virtual tables.

A key point though is that you created this gorgeous virtual table, and then never call it in the Result measure. This means either that it wasn’t needed in the first place or that the Result measure needs to be reworked. Based on your message above, it sounds like the former.

One of the skills that comes part and parcel with mastering virtual tables is learning what functions can and can’t call virtual tables, and how to work around the restrictions from the ones that can’t. For example VALUES can’t call a virtual table, but DISTINCT can, and in most cases those functions are equivalent.

So, bottom line - the virtual table wasn’t ultimately necessary here, but you added some hugely valuable tools to DAX toolbox. Nicely done! - you’ve earned the solve on this one.

  • Brian
1 Like