Forum Discussion
Measure not working with REMOVEFILTERS function
I have a simple table shown below. I have a measure calculation shown below.
| Column1 | Column2 | Column3 | Column4 |
| 1 | a2 | a4 | a6 |
| 1 | b2 | b4 | b6 |
| 0 | c2 | c4 | c6 |
| 0 | d2 | d4 | d6 |
| 1 | e2 | e4 | e6 |
| 1 | f3 | f5 | f7 |
| 1 | g3 | g5 | g7 |
| 0 | h3 | h5 | h7 |
| 1 | i3 | i5 | i7 |
| 0 | j3 | j5 | j7 |
The measure counts the number of values in "Column 1" but SHOULD NOT include the column 3 and 4 filters when counting. If I filter things in column 3 and 4, while Column2 has no filter it DOES NOT change the measure. This makes sense, below is a screenshot with 3 slicer filters for columns 2,3,4 with card for measure and overall table.
BUT, if I select few items in column2 AND then also select items in Column 3 or 4. The measure seems to break down. It DOES NOT function like its written, where it should remove filters on column3 and 4 for that measure. Below screenshot shows I selected 5 items in column 2 AND 1 item in column3 and 4. The measure here should be 5, buts its 1. I don't understand why this is happening?
What am I doing wrong? My end goal is to get a measure that counts column 1, while Column 2 has a filter and ignores filter on columns 3 and 4.
Hi, Chris1300
You can try 'Edit interactactions' to change how visuals interact in a Power BI report.
You need select slicer 'Column3',select 'Format'->'Edit interactactions' and then disable its interaction with other visual.This way the results of other visuals will not be filtered by the value of slicer 'Column3'.
Do the same for slicer 'Column4' and you will get the same Ideal Measure Output.
Best Regards,
Community Support Team _ Eason
3 Replies
Chris1300 , Make independent tables for these columns
c3 = distinct(Table[C3])
and then use them in filter as per need
CALCULATE( COUNT(Sheet1[Column1]) , filter(Sheet1, Sheet1[Column3] in values(c3[Column 3] ) ) )
Need of an Independent Table in Power BI - Exclude: https://youtu.be/lOEW-YUrAbE
No that does not work. I think I am not explaning my use case/problem clearly. Let me try again with a matrix of ideal measurement vs my measurement.
Recall my measurement is: Measure = CALCULATE( COUNT(Sheet1[Column1]) , REMOVEFILTERS(Sheet1[Column3],Sheet1[Column4]))
# of items selected in Column 2 (slicer) # of items selected in Column 3 (slicer) # of items selected in Column 4 (slicer) My Measure Output Ideal Measure Output Case 1 None
None
None 10 10 Case 2 4 None None 4 4 Case 3 None 4 2 10 10 Case 4 4 2 1 1 4 Case 5 4 2 None 2 4 Case 6 4 None 2 2 4 My current measure fails for case 4,5,6 and it works for case 1,2,3. I need a measurement that can output the ideal measurement column.Hope this helps explain my issue.Hi, Chris1300
You can try 'Edit interactactions' to change how visuals interact in a Power BI report.
You need select slicer 'Column3',select 'Format'->'Edit interactactions' and then disable its interaction with other visual.This way the results of other visuals will not be filtered by the value of slicer 'Column3'.
Do the same for slicer 'Column4' and you will get the same Ideal Measure Output.
Best Regards,
Community Support Team _ Eason
