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

Be one of the first to start using Fabric Databases. View on-demand sessions with database experts and the Microsoft product team to learn just how easy it is to get started. Watch now

Reply
Julia2023
Helper I
Helper I

ISFILTERED not working with manual selection

Hi, does anyone know how to fix the issue with the ISFILTERED function? I’ve created a measure to display a message if a data source (total two values) filter is applied and only one value is selected. However, the issue arises when I manually select both possible values from the filter; then I get the message that ‘value is not available.’ If I click on the ‘Select All’ option, it works fine and returns the value. How can I fix this filter?  Thanks

 

Budget =

 

VAR isSalesblank =

    IF(

        ISFILTERED(Country[Country]) ||

        ISFILTERED('Data Source'[Data Source]) ||

        ISFILTERED('NumberISO'[NumberKey]) ||

        ISFILTERED('Customer'[Customer]) ||

        ISFILTERED('Name' [Name]) ||

        ISFILTERED('Customers Clasification'[Classification]),

        BLANK(),

        [Budget] -- if none of the filters are applied

    )

VAR isnotSalesblank =

    IF(

        ISFILTERED(Country[Country]) ||

        ISFILTERED('Data Source'[Data Source]) ||

        ISFILTERED('NumberISO'[NumberKey]) ||

        ISFILTERED('Customer'[Customer]) ||

        ISFILTERED('Name' [Name]) ||

        ISFILTERED('Customers Clasification'[Classification]),

        "Value is not available at this level",

        [Budget for CY]

    )

RETURN

    IF(

        ISBLANK([POS $]),

        isSalesblank,

        isnotSalesblank

    )

 

 

2 ACCEPTED SOLUTIONS

Hi @Julia2023 

Assuming it is the Data source field that is causing problems I would replace both of them.

Budget =

 

VAR isSalesblank =

    IF(

        ISFILTERED(Country[Country]) ||

        Count('Data Source'[Data Source]) <> CountRows(All('Data Source')) ||

        ISFILTERED('NumberISO'[NumberKey]) ||

        ISFILTERED('Customer'[Customer]) ||

        ISFILTERED('Name' [Name]) ||

        ISFILTERED('Customers Clasification'[Classification]),

        BLANK(),

        [Budget] -- if none of the filters are applied

    )

VAR isnotSalesblank =

    IF(

        ISFILTERED(Country[Country]) ||

        Count('Data Source'[Data Source]) <> CountRows(All('Data Source')) ||

        ISFILTERED('NumberISO'[NumberKey]) ||

        ISFILTERED('Customer'[Customer]) ||

        ISFILTERED('Name' [Name]) ||

        ISFILTERED('Customers Clasification'[Classification]),

        "Value is not available at this level",

        [Budget for CY]

    )

RETURN

    IF(

        ISBLANK([POS $]),

        isSalesblank,

        isnotSalesblank

    )


If you are happy with this answer please mark as a solution for others to find !

Kudos are always appreciated! Check out our free Power BI video courses.

View solution in original post

v-kaiyue-msft
Community Support
Community Support

Hi @Julia2023 ,

 

I’d like to acknowledge the valuable input provided by the @SamWiseOwl . Their initial ideas were instrumental in guiding my approach. However, I noticed that further details were needed to fully understand the issue. 

 

According to the design of Power BI, when you select "select all", all data is displayed, the same logic as when there is no filter.

vkaiyuemsft_0-1722320343089.png  

 

vkaiyuemsft_1-1722320410129.png

 

When you select values ​​individually, the filter uses the logic of OR to filter the values.

vkaiyuemsft_2-1722320438290.png

 

Because of this, it causes unexpected results. You can distinguish the selected results by counting as @SamWiseOwl  said.

Measure = 
COUNT('Data Source'[Data Source])

Measure 2 = 
CALCULATE(COUNT('Data Source'[Data Source]),ALL('Data Source'))

vkaiyuemsft_3-1722320469796.png

If your Current Period does not refer to this, please clarify in a follow-up reply.

 

Best Regards,

Clara Gong

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

4 REPLIES 4
v-kaiyue-msft
Community Support
Community Support

Hi @Julia2023 ,

 

I’d like to acknowledge the valuable input provided by the @SamWiseOwl . Their initial ideas were instrumental in guiding my approach. However, I noticed that further details were needed to fully understand the issue. 

 

According to the design of Power BI, when you select "select all", all data is displayed, the same logic as when there is no filter.

vkaiyuemsft_0-1722320343089.png  

 

vkaiyuemsft_1-1722320410129.png

 

When you select values ​​individually, the filter uses the logic of OR to filter the values.

vkaiyuemsft_2-1722320438290.png

 

Because of this, it causes unexpected results. You can distinguish the selected results by counting as @SamWiseOwl  said.

Measure = 
COUNT('Data Source'[Data Source])

Measure 2 = 
CALCULATE(COUNT('Data Source'[Data Source]),ALL('Data Source'))

vkaiyuemsft_3-1722320469796.png

If your Current Period does not refer to this, please clarify in a follow-up reply.

 

Best Regards,

Clara Gong

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

Julia2023
Helper I
Helper I

Thank you, @SamWiseOwl . Where do you think this condition should be placed in my measure? Thanks. 

Budget =

 

VAR isSalesblank =

    IF(

        ISFILTERED(Country[Country]) ||

        ISFILTERED('Data Source'[Data Source]) ||

        ISFILTERED('NumberISO'[NumberKey]) ||

        ISFILTERED('Customer'[Customer]) ||

        ISFILTERED('Name' [Name]) ||

        ISFILTERED('Customers Clasification'[Classification]),

        BLANK(),

        [Budget] -- if none of the filters are applied

    )

VAR isnotSalesblank =

    IF(

        ISFILTERED(Country[Country]) ||

        ISFILTERED('Data Source'[Data Source]) ||

        ISFILTERED('NumberISO'[NumberKey]) ||

        ISFILTERED('Customer'[Customer]) ||

        ISFILTERED('Name' [Name]) ||

        ISFILTERED('Customers Clasification'[Classification]),

        "Value is not available at this level",

        [Budget for CY]

    )

RETURN

    IF(

        ISBLANK([POS $]),

        isSalesblank,

        isnotSalesblank

    )

Hi @Julia2023 

Assuming it is the Data source field that is causing problems I would replace both of them.

Budget =

 

VAR isSalesblank =

    IF(

        ISFILTERED(Country[Country]) ||

        Count('Data Source'[Data Source]) <> CountRows(All('Data Source')) ||

        ISFILTERED('NumberISO'[NumberKey]) ||

        ISFILTERED('Customer'[Customer]) ||

        ISFILTERED('Name' [Name]) ||

        ISFILTERED('Customers Clasification'[Classification]),

        BLANK(),

        [Budget] -- if none of the filters are applied

    )

VAR isnotSalesblank =

    IF(

        ISFILTERED(Country[Country]) ||

        Count('Data Source'[Data Source]) <> CountRows(All('Data Source')) ||

        ISFILTERED('NumberISO'[NumberKey]) ||

        ISFILTERED('Customer'[Customer]) ||

        ISFILTERED('Name' [Name]) ||

        ISFILTERED('Customers Clasification'[Classification]),

        "Value is not available at this level",

        [Budget for CY]

    )

RETURN

    IF(

        ISBLANK([POS $]),

        isSalesblank,

        isnotSalesblank

    )


If you are happy with this answer please mark as a solution for others to find !

Kudos are always appreciated! Check out our free Power BI video courses.

SamWiseOwl
Community Champion
Community Champion

Hi @Julia2023 ,

 

I have had this before when doing silly things with internal and external filters.

In the end what I did was:

Count(Country[Country]) <> CountRows(All(Country))

 

If the total rows in the Country column did NOT equal the unfiltered count then there must be a filter.

 

The weird thing about slicers is everything ticked IS applying a filter and so technically isn't the same as nothing ticked.

Let me know if it doesn't work!

If this helps please tick as a solution for others to find 🙂


If you are happy with this answer please mark as a solution for others to find !

Kudos are always appreciated! Check out our free Power BI video courses.

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!

November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Dec Fabric Community Survey

We want your feedback!

Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions.