Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Mo_
Frequent Visitor

Check if table is filtered but ignore one column

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

 

1 ACCEPTED SOLUTION
v-heq-msft
Community Support
Community Support

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

View solution in original post

2 REPLIES 2
v-heq-msft
Community Support
Community Support

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

Hi @v-heq-msft ,

 

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

 

Thank you so much!

 

Cheers

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors