Conditional Formatting when Slicer has Multiple Selections

[amconnel]

Hi,

Is there a way to dictate conditional formatting when you have multiple selections in a slicer? For example, if I have a matrix that shows data for companies A-D over several quarters. In the slicer, you are able to select which company you would like to view. When company A is selected, I want to data to appear with a yellow background. When company B is selected, I want the data to appear with a blue background. And when companies A and B are both selected, I want the data to be added together and appear with a green background. 

I am able to accomplish the correct background colors for individual companies, but when I have more than one company selected or all companies selected, there appears to be no conditional formatting when there should be.

Any ideas on how to fix this, perhaps in my conditional formatting measure that I am using?

Below are photos of my table, conditional formatting measure, and examples of what the matrix looks like with the selections mentioned above (conditional formatting has only been applied to Operating Income). 

[FrankAT]

Hi @amconnel ,

yes, I added the column SelectionCode manually in this small example, but this can also be done via DAX formula in a calculated column.

Binary coded means:

Each company gets a decimal binary value

• Company1:                1
• Company2:                2
• Company3:                4
• Company4:                8
• Company5:             16
• etc.

The sum of the binary values can be clearly assigned to a selection combination.

For example, if you select Company1 and Company3 in the slicer, the sum of the SelectionCode column = 5. In the SWITCH() function, a color can be assigned for the selection of Company1 and Company3 via the sum result.

Or the other way around: For the sum of 10, I know Company2 (decimal binary value: 2) and Company4 (decimal binary value: 8  ) were selected.

In addition to this concept, the desired functionality is also important from the implementation in your data model. Unfortunately, I don't know them.

With kind regards from the town where the legend of the 'Pied Piper of Hamelin' is at home
FrankAT (Proud to be a Datanaut)

[FrankAT · Accepted solution]

Hi @amconnel ,

you can do it with an additional binary coded data column:

If you use this column the following measure works as expected in conditional formatting:

Take a look at the attached PBIX file.

With kind regards from the town where the legend of the 'Pied Piper of Hamelin' is at home
FrankAT (Proud to be a Datanaut)

[amconnel]

I think I am understanding how this works. Could you explain the binary coded column you added a bit more? Did you manually input those numbers? 

For the sake of hoping for my initial question would be answered, I simplified the situation quite a lot. In reality, I have about 17.5k rows and 5 different options for company. I went into Power Query and created a column based on my company column and assigned the numbers. I created a formatting measure identical to yours, but the formatting is not showing still for some reason, no matter what is selected. 

Initially, I suspected it was due to taking the sum of the "selection code" column, so I adjusted the numbers in the formatting measure to be the summed amount rather than the distinct number. Still no return. 

Perhaps if I can come to understand this binary coded column a bit better, I can see where I have gone wrong here.

Thank you so much for your help so far!