Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
Hi,
I have a measure [EmptyCheck]. I apply this filter on a visual as [EmptyCheck] = 1.
Now, I want to write another measure [Amount] that sums up some column and that ignores the filter coming from the visual filter. I know you can do this with REMOVEFILTERS or ALL in a CALCULATE-statement if the filter comes from a column. But in my case, it comes from a measure.
How would I write the [Amount] measure to get it to ignore the filter on the visual coming from [EmptyCheck]?
Cheers
Solved! Go to Solution.
In Power BI, measures cannot directly remove a filter applied by another measure because they are calculated based on the current filter context, and they don't modify that context. However, you can work around this by reconsidering how the [EmptyCheck] measure is applied.
If you need the [Amount] measure to ignore the filter set by [EmptyCheck], you should ensure that [EmptyCheck] is not affecting the filter context in which [Amount] is being calculated. One way to do this is to use the `CALCULATE` function with `REMOVEFILTERS` or `ALL` in the [Amount] measure, but it won't override the visual-level filter applied by [EmptyCheck]. Instead, you need to structure your report so that [EmptyCheck] isn't affecting the calculation of [Amount].
Here is a conceptual approach to how you might achieve this:
1. Instead of applying [EmptyCheck] as a visual-level filter, use it within another measure to conditionally calculate the result only when [EmptyCheck] equals 1. For example:
Conditional Amount =
IF(
[EmptyCheck] = 1,
[Amount], // This is your original Amount measure
BLANK()
)
2. Then, use this new `Conditional Amount` measure in your visualizations instead of applying [EmptyCheck] directly as a filter. This allows the [Amount] calculation to remain unaffected by [EmptyCheck].
3. The original [Amount] measure should simply sum up the column you're interested in, without considering [EmptyCheck]:
Amount = SUM(Table[ColumnName])
This way, [Amount] is not impacted by [EmptyCheck], and you can use `Conditional Amount` in your reports where you want to display amounts only when [EmptyCheck] is 1.
Remember that the context in which measures are evaluated is crucial. Measures calculate values using the current filter context, and while they can't remove filters from other measures directly, you can design your report and measures in a way that the filters are applied conditionally, as shown above.
If this post helps, please consider Accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudo 👍
Visual-level filters only filter data for a given visual, whether it's a table, chart, card, slicer, and so on.
In Power BI, measures cannot directly remove a filter applied by another measure because they are calculated based on the current filter context, and they don't modify that context. However, you can work around this by reconsidering how the [EmptyCheck] measure is applied.
If you need the [Amount] measure to ignore the filter set by [EmptyCheck], you should ensure that [EmptyCheck] is not affecting the filter context in which [Amount] is being calculated. One way to do this is to use the `CALCULATE` function with `REMOVEFILTERS` or `ALL` in the [Amount] measure, but it won't override the visual-level filter applied by [EmptyCheck]. Instead, you need to structure your report so that [EmptyCheck] isn't affecting the calculation of [Amount].
Here is a conceptual approach to how you might achieve this:
1. Instead of applying [EmptyCheck] as a visual-level filter, use it within another measure to conditionally calculate the result only when [EmptyCheck] equals 1. For example:
Conditional Amount =
IF(
[EmptyCheck] = 1,
[Amount], // This is your original Amount measure
BLANK()
)
2. Then, use this new `Conditional Amount` measure in your visualizations instead of applying [EmptyCheck] directly as a filter. This allows the [Amount] calculation to remain unaffected by [EmptyCheck].
3. The original [Amount] measure should simply sum up the column you're interested in, without considering [EmptyCheck]:
Amount = SUM(Table[ColumnName])
This way, [Amount] is not impacted by [EmptyCheck], and you can use `Conditional Amount` in your reports where you want to display amounts only when [EmptyCheck] is 1.
Remember that the context in which measures are evaluated is crucial. Measures calculate values using the current filter context, and while they can't remove filters from other measures directly, you can design your report and measures in a way that the filters are applied conditionally, as shown above.
If this post helps, please consider Accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudo 👍
Check out the September 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.