Forum Discussion

andybrace's avatar
andybrace
Frequent Visitor
4 years ago
Solved

DAX Measure for conditional formatting - problem with filter context

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?
Need Help
  • Anonymous's avatar
    Anonymous
    4 years ago

    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

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    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