Forum Discussion
Filters
Hi All...
I have a scenario. I have a visual and a measure inside the visual. Now, Is it possible to have that measure not affected by visual level filter but affected by page level filter?
Hi jostnachs ,
For your measure %lost, I see that you say you've restricted status=lost in DAX, so in that case, whether status is filtered in a slicer, visual object level filter, or page level filter, it won't affect the value of that measure. Please see the test results below:Lost est_comm = CALCULATE( SUM('Table'[est_comm]), 'Table'[status] = "lost" )Without slicer or filter:
With slicer:
With Filters on this visual:
With Filters on this page:
In the case above, filtering on status will not affect your results for this measure, but filtering on other fields (such as filtering on policy_id) will still affect the value of the measure, for example:
However, if you want to use ALL or ALLSELECTED in the measure, then no matter what field is filtered, no matter if you use slicer or filter, it won't have any effect on the value returned by the measure.
Best Regards,
Dino Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.- Thank you so much for this.... i am gonna try this .
5 Replies
I think page level has the high priority. If you filter in the page level, then all the visuals in that page will be affected.
Hi jostnachs ,
After my test, if you want a measure to be unaffected by visual level filters, you need to use the ALL, ALLSELECTED or ALLEXCEPT functions in DAX, but this will eliminate the effects of all level filters because DAX cannot determine what level the filter is. The following is a screenshot of my test:ALL = SUMX(ALL('Table'), 'Table'[Value])Measure = CALCULATE( SUM('Table'[Value]), ALLEXCEPT('Table', 'Table'[ID]) )Therefore, you cannot achieve that the measure result is not affected by the visual object level filter but is affected by the page level filter.
I suggest you replace the visual level filter with a slicer, and then set the slicer to not affect the visual where the measure is located.
https://learn.microsoft.com/en-us/power-bi/visuals/power-bi-visualization-slicers?tabs=powerbi-desktop#control-which-page-visuals-are-affected-by-slicers
Alternatively, set the page level filter to a slicer, and then use the REMOVEFILTERS function in DAX to eliminate the effect of the visual level filter.
REMOVEFILTERS function (DAX) - DAX | Microsoft Learn
Best Regards,
Dino Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.Hi @ v-junyant-msft
Thank you so much for the help. I would like to explain you the exact scenario that I am having right now.
Below is the chart which shows
"no. of policies = sum(policy_id)","estimated commission=sum(est_comm)" and "estimated premium=sum(est_premium)" and "% lost="Estimated Lost Commission divided by the Previous Year Estimated Lost Commission"(where estimated lost commission = sum(est_comm) where status=lost).
now i want the below chart to be filtered to show me only for lost business not the entire business. but as u can see, the measure %lost is already showing me values for lost business and other fields are for entire business.
I have two approaches
aprroach1:
If i put up a visual filter to show me data only for status = lost, wouldnt that affect my measure %lost which is already showing me lost business? also, if i modify that measure with All/Allselected etc, then i am afraid that page level filters would also wont work on that measure.
Approach2:
I can simply modify the measures filtering them with status =lost and use them in the visual
"no. of policies = sum(policy_id) where status = lost","estimated commission=sum(est_comm) where tatus =lost" and "estimated premium=sum(est_premium) where status = lost" snd hence my page level filters and everything act normally
i am not sure if i am thinking correctly here. need help!
Hi jostnachs ,
For your measure %lost, I see that you say you've restricted status=lost in DAX, so in that case, whether status is filtered in a slicer, visual object level filter, or page level filter, it won't affect the value of that measure. Please see the test results below:Lost est_comm = CALCULATE( SUM('Table'[est_comm]), 'Table'[status] = "lost" )Without slicer or filter:
With slicer:
With Filters on this visual:
With Filters on this page:
In the case above, filtering on status will not affect your results for this measure, but filtering on other fields (such as filtering on policy_id) will still affect the value of the measure, for example:
However, if you want to use ALL or ALLSELECTED in the measure, then no matter what field is filtered, no matter if you use slicer or filter, it won't have any effect on the value returned by the measure.
Best Regards,
Dino Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
