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

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
Julia2023
Frequent Visitor

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
Frequent Visitor

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
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

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

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

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