Skip to main content
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
Julia1234
Helper I
Helper I

non-default value in 'greater than' slicer removes removefilters

Friday

Hi, I have a table with dataset (below)
In the table Production values are duplicated  for each year, region, Line, primary_product.
1. I created measure to calculate production accross year, region, Line, primary_product and ignore product and unit slicers .

Production_Meets = 
CALCULATE(
    SUMX(
        SUMMARIZE(
            Sheet1,
            Sheet1[year],
            Sheet1[region],
            Sheet1[Line],
            Sheet1[primary_product],
            "UniqueProduction", MAX(Sheet1[production])
        ),
        [UniqueProduction]
    ),
    REMOVEFILTERS(Sheet1[unit], Sheet1[product])
)

 

Issue: When Production 'Greater than' slicer is set to default (30), the measure shows correct value 213 (production value for bikeA primary_product across all products - frame , wheel)Julia1234_1-1752862515873.png

When When Production 'Greater than' slicer is set to non default (0,1,2..29,31..), the measure shows correct value 213. Then the Production_meet loses Removefilter ability and shows 180 (production value for bikeA primary_product across all products - frame only)Julia1234_2-1752862613986.png

If I set "Value filter behavior" in Model properties as Independent, then the formula will work, but I do not want to make this change on model level, as it affects other reports. How to fix production_meets calculations?

yearregionLineprimary_productproductunitproduction
2024NA123bikeAwheel%100.00
2024NA123bikeAwheelkg100.00
2024NA123bikeAbikeA%100.00
2024NA123bikeAwheeleach100.00
2024NA123bikeAframe%100.00
2024NA123bikeAframekg100.00
2024NA123bikeAframelb100.00
2024NA123bikeAframeeach100.00
2024NA123bikeAframetn100.00
2024NA567bikeAwheel%50.00
2024NA567bikeAwheelkg50.00
2024NA567bikeAwheellb50.00
2024NA567bikeAbikeA%50.00
2024NA567bikeAframe%50.00
2024NA567bikeAframekg50.00
2024NA567bikeAframelb50.00
2024NA567bikeAframeeach50.00
2024NA567bikeAframetn50.00
2024NA789bikeAwheel%30.00
2024NA789bikeAwheelkg30.00
2024NA789bikeAwheellb30.00
2024NA789bikeAwheeleach30.00
2024NA789bikeAbikeA%30.00
2024NA789bikeAframekg30.00
2024NA789bikeAframelb30.00
2024NA789bikeAframeeach30.00
2024NA789bikeAframetn30.00
2024NA333bikeAwheel%33.00
2024NA333bikeAwheelkg33.00
2024NA333bikeAwheellb33.00
2024NA333bikeAwheeleach33.00
2024NA333bikeAbikeA%33.00

 

 

Labels:
Message 1 of 5
144 Views
0
1 ACCEPTED SOLUTION
FBergamaschi
Post Prodigy
Post Prodigy
In response to Julia1234

Saturday

I am still a bit confused on what you want to obtain

 

Anyway, there is no way to avoid autoexist and chaning the DAX of your measure is useless for this purpose, as the autoexist is triggered by SUMMARIZECOLUMNS and you cannot control the query that is built from the visual (that will use SUMMARIZECOLUMNS, in fact, and therefore will have autoexist looming on it)

 

The only way is to separate the filters in multiple tables as I suggested. You say this is not an option

 

There is another way, the filter behaviour (Independent), again you do not wan to to pursue this.

 

I have no other options available, so my suggestion is to choose one of the two

 

If this helped, please consider giving kudos and mark as a solution

@mein replies or I'll lose your thread

consider voting this Power BI idea

Francesco Bergamaschi

MBA, M.Eng, M.Econ, Professor of BI

View solution in original post

Message 4 of 5
74 Views
4 REPLIES 4
Julia1234
Helper I
Helper I

Friday

   
Thank you @FBergamaschi  for prompt responce
1 - what coulmn is in the slicer? 'Production' is in the Greater than slicer. The other slicers are, year, region, primary_product, product
2 - are you working with a single table or a data model? Single flattened table but part of a larger model. Right now creating dimensions are not feasible
3 - in the first picture you have 213 as a total (you say it is correct even tough subtotals would sum up to 180) and after 180
213 - calculates production per year, region primary_product (BikeA) but omits product filter =frame and includes Lines without frame, as you can see lines in red, production for Line with 'wheel's is included (33)
180  - seems like product filter gets applied and only selected primary_product 'frame' is included
33  is filtered but should be included

Julia1234_0-1752865677651.png

 

Message 3 of 5
111 Views
0
FBergamaschi
Post Prodigy
Post Prodigy
In response to Julia1234

Saturday

I am still a bit confused on what you want to obtain

 

Anyway, there is no way to avoid autoexist and chaning the DAX of your measure is useless for this purpose, as the autoexist is triggered by SUMMARIZECOLUMNS and you cannot control the query that is built from the visual (that will use SUMMARIZECOLUMNS, in fact, and therefore will have autoexist looming on it)

 

The only way is to separate the filters in multiple tables as I suggested. You say this is not an option

 

There is another way, the filter behaviour (Independent), again you do not wan to to pursue this.

 

I have no other options available, so my suggestion is to choose one of the two

 

If this helped, please consider giving kudos and mark as a solution

@mein replies or I'll lose your thread

consider voting this Power BI idea

Francesco Bergamaschi

MBA, M.Eng, M.Econ, Professor of BI

Message 4 of 5
75 Views
Julia1234
Helper I
Helper I
In response to FBergamaschi

Saturday

Agreed.  Seems like Independent is the safe option for filtering in flat tables. Thank you so much!

Message 5 of 5
66 Views
0
FBergamaschi
Post Prodigy
Post Prodigy

Friday

Can you please clarify:

1 - what coulmn is in the slicer?

2 - are you working with a single table or a data model? In case more tables are involved please provide a sample

3 - in the first picture you have 213 as a total (you say it is correct even tough subtotals would sum up to 180) and after 180 (you say it is incorrect but the subtotals are the same as before), can you elaborate a bit more on this as I am a bit confused on the thing?

 

Without some clearer explanation, I do not understand how to help you

 

In any case, as you imply yourself, looks like an auto-exist issue. A solution might be as simple as creating dimensions for product, primary product, unit, production, year

 

you can use ALLNOBLANKROW ( Table[Column] ) to create them, connect them to the table and change the DAX referring to those columns in the dimensions and substitute the columns on the slicers with those belonging to the dimensions

 

Hope this helps, please answer my questions if it doesnot

 

If this helped, please consider giving kudos and mark as a solution

@me in replies or I'll lose your thread

consider voting this Power BI idea

Francesco Bergamaschi

MBA, M.Eng, M.Econ, Professor of BI

 

 

Message 2 of 5
127 Views

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

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

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.

Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.

View All