DAX Measure for conditional formatting - problem with filter context

[andybrace]

Hi,

On another post I found a solution to create a measure that can be used for conditional formatting:

ColourMeasure =

VAR _SELName = VALUES('tbl_MD_Business Matrix for Summary'[Global SBU - Std#])

RETURN

IF(MAX('tbl_MD_Business Matrix'[Global SBU - Std#]) IN _SELName,"Black","White")

The idea is to have a matrix with all businesses listed and then the one selected with the slicer on  'tbl_MD_Business Matrix for Summary'[Global SBU - Std#] will be highlighted using conditional formatting.

This was working fine, but then the report user asked for the slicer on 'tbl_MD_Business Matrix for Summary'[Global SBU - Std#] to only show the businesses in scope for their view (filtered on another higher level business slicer from an alternate table).  To do that I had to create some joins that ended up with the results being filtered but the matrix was only showing the one business selected.  I fixed that by editing the filter interactions.  But now I have told the matrix to ignore the filter context the formatting measure no longer works.

I assume I need to use something like KEEPFILTERS on the VAR _SELName??  But I can't work out the syntax.

Or maybe there is a better way to achieve the same thing?

[v-jayw-msft · Accepted solution]

Hi @andybrace ,

You could add the filter conditions by using CALCULATETABLE() and FILTER() function.

For example:

VAR _SELName =
CALCULATETABLE (
VALUES ( 'tbl_MD_Business Matrix for Summary'[Global SBU - Std#] ),
FILTER (
ALLSELECTED ( 'tbl_MD_Business Matrix for Summary' ),
'tbl_MD_Business Matrix for Summary'[business]
= SELECTEDVALUE ( 'alternate'[business] )
)
)

Best Regards,

Jay

[lbendlin]

Please provide sanitized sample data that fully covers your issue. If you paste the data into a table in your post or use one of the file services it will be easier to assist you. I cannot use screenshots of your source data.

Please show the expected outcome based on the sample data you provided. Screenshots of the expected outcome are ok.

https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523