Matrix visual color conditional formatting

[goalie_]

Hi,

I'm relatively new to Power BI. I am looking to highlight unique rows in a matrix visual such as below. In this case, I'd like to have the Carbs and Protein rows highlighted but NOT the sugar, fat and fiber.

		Rice Type
		White Short Grain	Brown short grain	White long grain
Nutrition Type	Carbs	50 highlighted	45 highlighted	39 highlighted
	Sugar	1	1	1
	Protein	10 highlighted	11 highlighted	12  highlighted
	Fat	1	1	1
	Fiber	3	3	3

In the visualizations pane, the entered info is

• Rows: Nutrition Type
• Columns: Rice Type
• Values: Value

The data source is as follows.

Rice Type	Nutrition Type	Value
White Short Grain	Carbs	50
White Short Grain	Sugar	1
White Short Grain	Protein	10
…	…	…

I know I'll need to create some kind of measure and use conditional formatting but I'm not sure how to proceed.

[amitchandak]

@goalie_ , Not sure I got it. You can create a color measure and use that with Fields option in conditional formatting

Color  = if(FIRSTNONBLANK(Table[Year],2014) <=2016 && AVERAGE(Sales[Sales Amount])<170 
,"lightgreen",if(FIRSTNONBLANK(Table[Year],2014)>2018,"red","yellow"))
Color sales = if([Sales Today] -[sales yesterday]>0,"green","red")

https://radacad.com/dax-and-conditional-formatting-better-together-find-the-biggest-and-smallest-num...
https://docs.microsoft.com/en-us/power-bi/desktop-conditional-table-formatting#color-by-color-values

[parry2k]

@goalie_ I have a similar solution done a long time ago, see attached. In this, if values are different than it highlights whereas in your if the value is the same that highlights the row, you can change reverse logic in the Dax function and implement the solution in your model.

It is pretty straight forward, if you still run into an issue, let me know.

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!

⚡Visit us at https://perytus.com, your one-stop shop for Power BI related projects/training/consultancy.⚡

[goalie_]

In terms of the example you showed me, if I had C2, S1, Server 2 with an amount of 200 and only selected Servers 1,2,3, the result is below:

It highlights the C2, S1 row because Server 4 still has a different value for it. For what I'm trying to do, I'd like it to be clear. Hopefully that makes more sense.

[parry2k]

@goalie_ sure I will tweak the measure, I think that will do it. stay tuned

[parry2k · Accepted solution]

@goalie_ change following measure

# of servers = 
COUNTROWS ( 
        CALCULATETABLE( 
            VALUES ( Amount[Server] ),
            ALLSELECTED ( Amount ) 
        ) 
    ) 

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!

⚡Visit us at https://perytus.com, your one-stop shop for Power BI related projects/training/consultancy.⚡

[goalie_]

Hi @parry2k , it works for the most part. I tried it on my dataset. There are some where it's the same but it still becomes highlighted. Any ideas?

I'm not entirely clear on how conditional formatting applies by using the measure. Does it look at it columnwise, or rowwise or some combination? I might be able to figure it out from there. Thank you again.

Edit: I'm trying to upload a picture  but it won't let me. I made this table to show what kind of output I'm getting for some of the rows

|0.140661000|	|0.140661000	|0.140661000
|0.360054000	|0.360054000	|0.360054000

[goalie_]

I think I narrowed it down. I only see it happening when there's three options for the columns in slicer that are selected.

[goalie_]

Still not sure why it's happening. But I found a solution. Incase anyone wants to know for the future, I encompassed the original DAX statement with the round( ) function upto 10 decimal points. And that seemed to do the trick.

Thank you again @parry2k 

[goalie_]

Thank you so much, but I'm not there yet. I want the highlighting to apply to selected column values via a slicer. I think we're on the right track. So my question is how would I specify for specific column values that are narrowed down via a slicer?

For example, let's say I have more Rice Types(Jasmine, black, flaked, etc...) in addition to Brown short, white short, and white long. Now if I slice it to 3 of them, I only want the highlighting rules to only apply to the ones selected, not the entirety of the table, which is what it is currently doing.

[parry2k]

@goalie_ what is the business logic which rows get highlighted over another?

[goalie_]

The contents of the row contain different values for each column.

For example in carbs, there's 50, 45, 39 which are all different numbers so it should be highlighted. IF they were 50, 45, 50, then the row would need to be highlighted. However, if they all had the same value 50,50,50 then it shouldn't be highlighted. 

Edit: there could be other rice types too. But with slicers, focused on the three mentioned above.