Forum Discussion
Make measure ignore specific filter
This should be simple but I just can’t solve it :) I have a report page with several filters and a measure Amounts = SUM(Table.Amount). Now I’d like Amounts to ignore just one of the filters, a Page...
This is not really an issue. It is clearly explained in this article from Alberto. It is happening because of Auto Exist feature in DAX.
Besides, creating a seperate dimension table is not really a workaround, but rather a best practice. A Star Schema is always considered best when working with Power BI Datamodel. This is what makes it more powerful!
Hey guys!
It's dead simple, really. Use the ALL function to remove a filter on a specific column like this:
Measure = calculate(Expression,ALL(column-you-want-to-remove-filter))
That's it. ALL, used inside a CALCULATE expression, works like REMOVEFILTER.
Hi Greg,
My measure is giving wrong column sub total but correct row sub total.
My Actual Measure in the report:
Measure =
VAR Y=
CALCULATE(SUMX(VALUES('CurrentTimeEntry Details'),[ActualHours]),ALLSELECTED('Staff Details'[Employee Name])]))
VAR X = CALCULATE(SUM ('CurrentTimeEntry Details'[ActualHours]))
Return x/Y
Output I am getting:
| Employee | Project1 | Project2 | Total |
| A | 0.3 | 0 | 0.3 |
| B | 0.21 | 0.19 | 0.25(NOT CORRECT) |
| C | 0 | 0.3 | 0.3 |
| Total | 0.51 | 0.49 | 1 |
It is coming wrong as the measure is behaving at the backend like below ie ( Filtering across the project)
Measure =
VAR Y=
CALCULATE(SUMX(VALUES('CurrentTimeEntry Details'),[ActualHours]),ALLSELECTED('Staff Details'[Employee Name]),ALLSELECTED('Project Details'[Complete Project Detail])) <--- Filtering
VAR X = CALCULATE(SUM ('CurrentTimeEntry Details'[ActualHours]))
Return x/Y
Can you please suggest the solution as I am unable to avoid filtering and fix this
Can you please suggest the solution as I am unable to avoid filtering and fix this