Maxtrix conditional formatting

[Back2Basics]

I have a matrix that is using data from two tables. Both tables have two columns; 'Unique ID' plus either 'Action' or 'Outcome'.

Both of the tables have multiple rows per ID as each Id can have many actions or outcomes. I then have a third table with all one of each ID and relationship set up.  

The maxtrix then plots the actions against outcome, with the count of ID (from the table with just ID's) as the value. This works fine.

What I would like to do is to have a slicer, or table if this is better, for the Unique ID - so that when selected the table values don't change but the matrix value backgrounds change colour to show the relevant parts of the matrix for that Unique ID. 

eg. 

	outcome A	outcome B	outcome C	outcome D
action A	5	6	1	15
action B	1	6	12	3
action C	12	0	3	7

then when an ID selected from the slicer/table it would show (I'd like background colour but used text colour in the example due to the options available for posting this):

	outcome A	outcome B	outcome C	outcome D
action A	5	6	1	15
action B	1	6	12	3
action C	12	0	3	7

I have tried a couple of things with conditional formatting but can't get this to work. Anyone done this before?

[MFelix · Accepted solution]

Hi @Back2Basics ,

Believe that for this case you need to create a unrelated table with the unique ID's then you need to create a measure similar to this one:

Condittional formatting = IF(SELECTEDVALUE(SlicerID[ID]) in VALUES(UniqueID[ID]), 1)

Now on the condittional formating select rule and value equal 1 should give something similar to:

[Back2Basics]

Hi @MFelix 

I succesfully used this, as I've said, in a report - worked perfectly. However, the report became corrupted and I had to start again. I did exactly the same, everything is as your suggestion - but for some reason the conditional formatting will not function this time round. 

It's not essential that the report has this but it is a very useful function. Do you know of any reason, or setting, that might mean this wouldn't work for one report when it does work in an identical report?

[MFelix]

Hi @Back2Basics,

The questions here can be of context or filtering. But without any specific information is difficult to pin point.

Can you please share a mockup data or sample of your PBIX file. You can use a onedrive, google drive, we transfer or similar link to upload your files.

If the information is sensitive please share it trough private message.

[MFelix · Accepted solution]

Hi @Back2Basics ,

Believe that for this case you need to create a unrelated table with the unique ID's then you need to create a measure similar to this one:

Condittional formatting = IF(SELECTEDVALUE(SlicerID[ID]) in VALUES(UniqueID[ID]), 1)

Now on the condittional formating select rule and value equal 1 should give something similar to:

[Back2Basics]

Thanks @MFelix this is great. One thing that is a little annoying is that the matrix has a default formatting, with seemingly random cells getting the background - but works perfectly once the slicer has been selected. 

[MFelix]

Hi @Back2Basics,

What do you mean randomly get background? 

[Back2Basics]

When nothing is selected from the slicer, there are some cells that have a background coloured already. But I'm unsure what they are relevant to

[MFelix · Accepted solution]

Hi @Back2Basics ,

That is related with the fact that probably those lines include all values on the slicer.

Try to change your measure to:

Condittional formatting =
IF (
    ISFILTERED ( SlicerID[ID] ),
    IF ( SELECTEDVALUE ( SlicerID[ID] ) IN VALUES ( UniqueID[ID] ), 1 )
)

This will make sure you have selections on the slicer.

[Back2Basics]

That is great, thanks. Worked a treat. 

I've not done much with measures, so getting help like this goes a long way for me. Thanks