Check if table is filtered but ignore one column

[Mo_]

Hi,

I have a variable in a measure that is supposed to check whether a table is filtered or not and then return a string:

Var _Item = IF( ISFILTERED( DIM_item ), "| item" ) 

However, I need to apply a global filter on all report pages on the column DIM_item[relevant]. Doing this makes the variable always return the string since the condition is always true. However, this is not what I need. I want the variable to ignore the filter von DIM_item[relevant].

I already tried:

Var _Item = IF( ISFILTERED( DIM_item ) && NOT ISFILTERED( DIM_item[isKPIRelevant] ), "| item" ) 

But when filtering another column in DIM_item, the variable returns nothing (which makes sense since the condition is not true).

Anyone having another suggestion?

Cheers

[v-heq-msft · Accepted solution]

Hi @Mo_ ,
Based on your description, I have created the following MEASURES that you can try to see if they fulfill your requirements.

Measure = 
IF( 
    CALCULATE( COUNTROWS( DIM_item ), ALLSELECTED( DIM_item ), REMOVEFILTERS( DIM_item[isKPIRelevent] ) ) <> CALCULATE( COUNTROWS( DIM_item ), ALLSELECTED( DIM_item ) ),
    "| item"
)

Measure 2 = 
IF(
    ISFILTERED(DIM_item) &&
    NOT HASONEFILTER(DIM_item[isKPIRelevent]), "| item")

Measure 3 = 
VAR IsKPIRelevantFiltered = NOT ISFILTERED(DIM_item[isKPIRelevent])
VAR _Item =
    IF(
        ISFILTERED(DIM_item) &&
        IsKPIRelevantFiltered
    , "| item")
RETURN
_Item

If these don't meet your requirements, please provide test data or pbix files and desired results so we can help you faster. Please hide sensitive information in advance.

Best regards,

Albert He

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[Mo_]

Hi @v-heq-msft ,

you first suggestion works, except I had to replace "<>" with "=".

Thank you so much!

Cheers