Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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?
Solved! Go to 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:
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHi @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.
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHi @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?
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.
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHi @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:
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsThanks @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.
Hi @Back2Basics,
What do you mean randomly get background?
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsWhen 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
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.
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsThat 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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
112 | |
99 | |
73 | |
72 | |
49 |
User | Count |
---|---|
145 | |
109 | |
109 | |
90 | |
64 |