Forum Discussion
Solved
DAX Ignore filter context
Hi I have the following table
| UPRN | High or Low MPV_ | NPV Net Rent_ |
| 18 | High | £122,768.31 |
| 26 | High | £109,681.07 |
| 92 | High | £116,317.17 |
| 109 | High | £81,850.06 |
| 117 | High | £119,385.69 |
| 125 | High | £109,950.07 |
| 133 | High | £111,209.20 |
| 183 | High | £124,487.94 |
| 224 | High | £152,263.08 |
| 266 | High | £161,916.57 |
| 315 | High | £135,789.06 |
| 1678 | High | £164,847.35 |
| 1694 | High | £169,883.22 |
| 1701 | High | £155,412.71 |
| 1719 | High | £152,977.25 |
| 1735 | High | £168,884.99 |
| 1751 | High | £162,613.36 |
| 1769 | High | £160,459.04 |
| 1785 | High | £161,966.50 |
And this measure that obtains the Avg NPV_
Avg NPV_ =
VAR NetRentNPV = SUM('NPV Table_'[NPV Net Rent_])
VAR Prop = COUNTROWS('NPV Table_')
VAR AvgNPV = DIVIDE(NetRentNPV, Prop)
RETURN
AvgNPV
However when I add the measure to the table filter context is applied.
| UPRN | High or Low MPV_ | NPV Net Rent_ | Avg NPV_ |
| 18 | High | £122,768.31 | £122,768 |
| 26 | High | £109,681.07 | £109,681 |
| 92 | High | £116,317.17 | £116,317 |
| 109 | High | £81,850.06 | £81,850 |
| 117 | High | £119,385.69 | £119,386 |
| 125 | High | £109,950.07 | £109,950 |
| 133 | High | £111,209.20 | £111,209 |
| 183 | High | £124,487.94 | £124,488 |
| 224 | High | £152,263.08 | £152,263 |
| 266 | High | £161,916.57 | £161,917 |
| 315 | High | £135,789.06 | £135,789 |
| 1678 | High | £164,847.35 | £164,847 |
| 1694 | High | £169,883.22 | £169,883 |
| 1701 | High | £155,412.71 | £155,413 |
| 1719 | High | £152,977.25 | £152,977 |
| 1735 | High | £168,884.99 | £168,885 |
| 1751 | High | £162,613.36 | £162,613 |
| 1769 | High | £160,459.04 | £160,459 |
| 1785 | High | £161,966.50 | £161,967 |
Can someone please remind me of the DAX function I need to ignore filter context , so that the results look more like this.
| UPRN | High or Low MPV_ | NPV Net Rent_ | Avg NPV_ |
| 18 | High | £122,768.31 | £106,515 |
| 26 | High | £109,681.07 | £106,515 |
| 92 | High | £116,317.17 | £106,515 |
| 109 | High | £81,850.06 | £106,515 |
| 117 | High | £119,385.69 | £106,515 |
| 125 | High | £109,950.07 | £106,515 |
| 133 | High | £111,209.20 | £106,515 |
| 183 | High | £124,487.94 | £106,515 |
| 224 | High | £152,263.08 | £106,515 |
| 266 | High | £161,916.57 | £106,515 |
| 315 | High | £135,789.06 | £106,515 |
| 1678 | High | £164,847.35 | £106,515 |
| 1694 | High | £169,883.22 | £106,515 |
| 1701 | High | £155,412.71 | £106,515 |
| 1719 | High | £152,977.25 | £106,515 |
| 1735 | High | £168,884.99 | £106,515 |
| 1751 | High | £162,613.36 | £106,515 |
| 1769 | High | £160,459.04 | £106,515 |
| 1785 | High | £161,966.50 | £106,515 |
thnak you
Richard
Hi cottrera ,
The ALL function can remove any filter context. You can try below measure:
Avg NPV_ = CALCULATE( VAR NetRentNPV = SUM('NPV Table_'[NPV Net Rent_]) VAR Prop = COUNTROWS('NPV Table_') VAR AvgNPV = DIVIDE(NetRentNPV, Prop) RETURN AvgNPV, ALL() )Please mark this as solution and give Kudos, if this helps resolve your issue.
Thank you~
4 Replies
Hi cottrera ,
The ALL function can remove any filter context. You can try below measure:
Avg NPV_ = CALCULATE( VAR NetRentNPV = SUM('NPV Table_'[NPV Net Rent_]) VAR Prop = COUNTROWS('NPV Table_') VAR AvgNPV = DIVIDE(NetRentNPV, Prop) RETURN AvgNPV, ALL() )Please mark this as solution and give Kudos, if this helps resolve your issue.
Thank you~
Thank you for your quick response
try
AvgNPV_No filters = calculate[Avg NPV_], removefilters('Table UPRN'))or
AvgNPV_All = calculate[Avg NPV_], ALL('Table UPRN'))whatever table has that first column in
Thank you also for contributing
