topic Re: non-default value in 'greater than' slicer removes removefilters in DAX Commands and Tips

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

Julia1234 — Fri, 18 Jul 2025 18:30:32 GMT

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)

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)

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?

year	region	Line	primary_product	product	unit	production
2024	NA	123	bikeA	wheel	%	100.00
2024	NA	123	bikeA	wheel	kg	100.00
2024	NA	123	bikeA	bikeA	%	100.00
2024	NA	123	bikeA	wheel	each	100.00
2024	NA	123	bikeA	frame	%	100.00
2024	NA	123	bikeA	frame	kg	100.00
2024	NA	123	bikeA	frame	lb	100.00
2024	NA	123	bikeA	frame	each	100.00
2024	NA	123	bikeA	frame	tn	100.00
2024	NA	567	bikeA	wheel	%	50.00
2024	NA	567	bikeA	wheel	kg	50.00
2024	NA	567	bikeA	wheel	lb	50.00
2024	NA	567	bikeA	bikeA	%	50.00
2024	NA	567	bikeA	frame	%	50.00
2024	NA	567	bikeA	frame	kg	50.00
2024	NA	567	bikeA	frame	lb	50.00
2024	NA	567	bikeA	frame	each	50.00
2024	NA	567	bikeA	frame	tn	50.00
2024	NA	789	bikeA	wheel	%	30.00
2024	NA	789	bikeA	wheel	kg	30.00
2024	NA	789	bikeA	wheel	lb	30.00
2024	NA	789	bikeA	wheel	each	30.00
2024	NA	789	bikeA	bikeA	%	30.00
2024	NA	789	bikeA	frame	kg	30.00
2024	NA	789	bikeA	frame	lb	30.00
2024	NA	789	bikeA	frame	each	30.00
2024	NA	789	bikeA	frame	tn	30.00
2024	NA	333	bikeA	wheel	%	33.00
2024	NA	333	bikeA	wheel	kg	33.00
2024	NA	333	bikeA	wheel	lb	33.00
2024	NA	333	bikeA	wheel	each	33.00
2024	NA	333	bikeA	bikeA	%	33.00

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

FBergamaschi — Fri, 18 Jul 2025 18:44:38 GMT

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

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

Julia1234 — Fri, 18 Jul 2025 19:17:22 GMT

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

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

FBergamaschi — Sat, 19 Jul 2025 13:04:48 GMT

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

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

Julia1234 — Sat, 19 Jul 2025 13:40:52 GMT

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