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
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
)
Solved! Go to Solution.
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.
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.
When you select values individually, the filter uses the logic of OR to filter the values.
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'))
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.
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.
When you select values individually, the filter uses the logic of OR to filter the values.
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'))
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.
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.
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.
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
113 | |
96 | |
90 | |
82 | |
69 |
User | Count |
---|---|
157 | |
125 | |
116 | |
111 | |
95 |