Forum Discussion
Variable still filtered in visual where using REMOVEFILTERS
Hi, I'm importing a table of employees with their categories, fix salary & variables. I set up the matrix with categories in line, sex in column and I want this one to be filtered on contract type, categorie <> "n.a" and population = "with variable" so I set those as visual filters. I want to display staff numbers and total staff numbers in front where averages are displayed normally according to filters. So I created 2 measures :
StaffNum = COUNTA([ID])
ALLStaffNum = CALCULATE(COUNTA([ID]), REMOVEFILTERS([Variable]))
I checked the numbers and all goes ok, except one row ...when I filter it manually I get StaffNum = 36 and ALLStaffNum = 37 but the visual keep displaying 36 and 36 ...
When I remove the visual filter Variable>0 I get the 37 ... What I don't understand is the the measures gave the expected results for all the visual except one data and I can't figure out what's different. I changed all the data to proper format in powerQuery in case but it doesn't change anything... I'm driving crazy..
hahah I think we're all getting confused here.
I'm seeing two different evaluations here. Evaluation 01 is to return all values where is greater than 0. Evaluation 02 is to return all values regardless of amount when a "variable" is selected.
Unfiltered, my results are 5. However, if I filtered for only 'A' and 'B' I get the expected 4.
I've attached a pbix for reference.
This behaviour happens when you are applying and manipulating filters on a single table, it is known as auto-exist and you can find a full explanation at https://www.sqlbi.com/articles/understanding-dax-auto-exist/.
There are a couple of work arounds you can use. In the model view, select Model rather than Table and then select the root Semantic Model element. Change the Value filter behaviour to independent.
Alternatively, don't use a model with just one flat table. Create a separate dimension table linked to your main table and then use that dimension table in all visuals and filters instead of filtering on the main table. In your simplified example you could create a Plan Table by using
Plan = DISTINCT( 'Table'[Plan] )link that to 'Table' and then use the Plan[Plan] column everywhere instead of 'Table'[Plan].
16 Replies
johnt75Super User
This behaviour happens when you are applying and manipulating filters on a single table, it is known as auto-exist and you can find a full explanation at https://www.sqlbi.com/articles/understanding-dax-auto-exist/.
There are a couple of work arounds you can use. In the model view, select Model rather than Table and then select the root Semantic Model element. Change the Value filter behaviour to independent.
Alternatively, don't use a model with just one flat table. Create a separate dimension table linked to your main table and then use that dimension table in all visuals and filters instead of filtering on the main table. In your simplified example you could create a Plan Table by using
Plan = DISTINCT( 'Table'[Plan] )link that to 'Table' and then use the Plan[Plan] column everywhere instead of 'Table'[Plan].
I'll try to anonymize something
I don't know why but I think there is a conflict between the filter "with variable" and the "variable>0" filter. It's like when I activate the "variable>0" the filter "with variable" go wrong ... actually the "with variable" column contains different variable plans A, B, C ...
I tryed with this set of data
Number = COUNTA('Table'[ID])NumberALL = CALCULATE(COUNTA('Table'[ID]), REMOVEFILTERS('Table'[Amount]))With the Data below, let's say I want "A" and "B", so 4 people in total and 3 with amount > 0
When you add the "amount>0" filter on the visual, the option B dispears from the visual filter and you get number = 3 and numberall = 3
I don't understand why but I understand I have to do the opposite, instead of removing a filter, I should explicitely add a filter in the measures. It scares me like hell because I have the feeling I can't trust my figures when using visual filters
IDVariable PlanAmount
1 A 10 2 A 20 3 A 30 4 B 0 5 C 10 6 C 20 - Ashish_Mathur
HI,
Filter the visual on variable A and B. Try these measures
Measure = countrows(Data)
Measure1 = sum(Data[Amount])
Measure2 = countrows(filter(values(Data[ID]),[Measure1]>0)
Drag measure and measure2 to a card visual.
Hope this helps.
- cengizhanarslan
Instead of applying Variable > 0 as a visual filter and trying to override it in the measure, remove it from the visual filter panel entirely and push the filtering into the measure that needs it:
StaffNum = CALCULATE ( COUNTA ( 'Table'[ID] ), 'Table'[Variable] > 0 ) Hi, here is a sample.
IDPLanamout
1 a 1 2 b 1 3 c 0 I'm seeking for an explanation of the mechanics : given a matrix with the visual filter (amount>0)
the measure
TotalNum = CALCULATE(COUNTA('Table'[ID]),REMOVEFILTERS('Table'[amout]))
gives the expected result by removing the filter amount (result: 3, expected: 3)
given the same matrix with 2 filters : (amount>0) and (plan is A or C), the same measure goes wrong (result:1 expected:2)
PS : didn't find where to attach a file
- Ashish_Mathur
Hi,
The answer should be 1. Of A and C, only A has an amount > 0.
no, to me it should be 2 A&C because the measure contains REMOVEFILTERS([amount])
v-veshwara-msftCommunity Support
Hi Usix ,
Thanks for reaching out to Microsoft Fabric Community.Just checking in to see if you query is resolved and if any responses were helpful.
Otherwise, feel free to reach out for further assistance.Thank you.
- v-veshwara-msft
Hi Usix ,
We wanted to kindly follow up regarding your query. If you need any further assistance, please reach out.
Thank you.
