DAX Ignore filter context

cottrera — Mon, 13 May 2024 07:40:16 GMT

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

Re: DAX Ignore filter context

PurpleGate — Mon, 13 May 2024 08:05:09 GMT

try

AvgNPV_No filters = calculate[Avg NPV_], removefilters('Table UPRN'))

AvgNPV_All = calculate[Avg NPV_], ALL('Table UPRN'))

whatever table has that first column in

Re: DAX Ignore filter context

xifeng_L — Mon, 13 May 2024 08:09:04 GMT

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~

Re: DAX Ignore filter context

cottrera — Mon, 13 May 2024 08:13:45 GMT

Thank you for your quick response

Re: DAX Ignore filter context

cottrera — Mon, 13 May 2024 08:14:05 GMT

Thank you also for contributing

topic Re: DAX Ignore filter context in DAX Commands and Tips

DAX Ignore filter context

Re: DAX Ignore filter context

Re: DAX Ignore filter context

Re: DAX Ignore filter context

Re: DAX Ignore filter context