Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
cottrera
Post Prodigy
Post Prodigy

DAX Ignore filter context

Hi I have the following table

UPRNHigh or Low MPV_NPV Net Rent_
18High£122,768.31
26High£109,681.07
92High£116,317.17
109High£81,850.06
117High£119,385.69
125High£109,950.07
133High£111,209.20
183High£124,487.94
224High£152,263.08
266High£161,916.57
315High£135,789.06
1678High£164,847.35
1694High£169,883.22
1701High£155,412.71
1719High£152,977.25
1735High£168,884.99
1751High£162,613.36
1769High£160,459.04
1785High£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.
UPRNHigh or Low MPV_NPV Net Rent_Avg NPV_
18High£122,768.31£122,768
26High£109,681.07£109,681
92High£116,317.17£116,317
109High£81,850.06£81,850
117High£119,385.69£119,386
125High£109,950.07£109,950
133High£111,209.20£111,209
183High£124,487.94£124,488
224High£152,263.08£152,263
266High£161,916.57£161,917
315High£135,789.06£135,789
1678High£164,847.35£164,847
1694High£169,883.22£169,883
1701High£155,412.71£155,413
1719High£152,977.25£152,977
1735High£168,884.99£168,885
1751High£162,613.36£162,613
1769High£160,459.04£160,459
1785High£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.

 

UPRNHigh or Low MPV_NPV Net Rent_Avg NPV_
18High£122,768.31£106,515
26High£109,681.07£106,515
92High£116,317.17£106,515
109High£81,850.06£106,515
117High£119,385.69£106,515
125High£109,950.07£106,515
133High£111,209.20£106,515
183High£124,487.94£106,515
224High£152,263.08£106,515
266High£161,916.57£106,515
315High£135,789.06£106,515
1678High£164,847.35£106,515
1694High£169,883.22£106,515
1701High£155,412.71£106,515
1719High£152,977.25£106,515
1735High£168,884.99£106,515
1751High£162,613.36£106,515
1769High£160,459.04£106,515
1785High£161,966.50£106,515

 

thnak you

Richard

1 ACCEPTED SOLUTION
xifeng_L
Super User
Super User

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~

View solution in original post

4 REPLIES 4
xifeng_L
Super User
Super User

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

PurpleGate
Resolver III
Resolver III

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

 

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.