DAX Formula Help

[anthonjhnon1]

Link to files in Onedrive

Haven't been here in a long time as I thought my DAX skills were supberb. However this is stomping me to the ground. As when I apply a filter on major event through the filter pane I'm not getting what I expect. I need a function to ignore filters from the filter pane or slicers.

1. If no filtering is done on [MajorEvent], I want to sum up CI value. Output should be 600. (Working)

2. If I filter [major event] = 'N', I want the sum of CIs and and sum of nonME_CIs, even for cases with a [MajorEvent] = 'Y'. Output should be 173. (Not Working, I'm only getting 53 and it should be 173)

3. If I filter [MajorEvent] = 'Y', I want to sum CIs minus sum of nonME_CIs. Output should be 427. (Working)

[anthonjhnon1]

@Natarajan_M I marked this as a solution because it does work and works exactly as I want it to. However its not the ultimate solution for me. I need to share this DAX measure with tons of other reports and this solution will require all those reports to include a ME table and Calendar table (which I most reports have). Can you help me understand why adding those two tables work?

[Natarajan_M]

Hi @anthonjhnon1 

If you're using REMOVEFILTERS or CALCULATETABLE in DAX, never apply it directly to the fact table. Doing so clears all filter context, including Year, CaseID, and any other active filters on the page.

Instead:

1. Create a dimension table for the attribute you want to control (e.g., Major Event, Status, Flag).
2. Build your slicer using that dimension table.
3. Apply REMOVEFILTERS on the dimension table, not the fact table.

The dimension-to-fact relationship propagates the filter automatically. Your DAX only needs to selectively override the one dimension you wish to ignore, ensuring everything else remains correctly filtered.

Also  this isn't just a correctness issue, it's a performance issue too. Your fact table might have 10 million rows. Your Major Event dim has 2. When Power BI evaluates REMOVEFILTERS on a 2-row dim table and lets the relationship push that down to the fact, it's a fraction of the work compared to scanning the filter column across 10 million fact rows directly.

Bottom line: If you find yourself writing REMOVEFILTERS(FactTable), that's a signal that your model requires a dimension table.

Thanks 

[anthonjhnon1]

After you message I started to realize why it was doing it. But I really appreciate the help, I'll remember this. 

[johnt75]

The issue is that variables in DAX aren't really variable, they're constants. They are only ever evaluated once, so trying to evaluate them again with a modified filter context will not work, they will still have their original value. If you need to change the filter context then you need to include the full definition of the variable again inside the calculate.

[cengizhanarslan]

Could you try the measure below:

CIs Test =
VAR _MajorEvent =
    SELECTEDVALUE ( v_HistoricalOutageData_c[MajorEvent] )

VAR _TotalCI =
    CALCULATE (
        SUM ( v_HistoricalOutageData_c[TotalCustomersAffected] ),
        REMOVEFILTERS ( v_HistoricalOutageData_c[MajorEvent] )
    )

VAR _TotalNonME =
    CALCULATE (
        SUM ( v_HistoricalOutageData_c[nonME_CI] ),
        REMOVEFILTERS ( v_HistoricalOutageData_c[MajorEvent] )
    )

RETURN
    SWITCH (
        _MajorEvent,
        "N", _TotalNonME,
        "Y", _TotalCI - _TotalNonME,
        _TotalCI
    )

[Natarajan_M]

Hi @anthonjhnon1  Did you try the remove filters at table level ? if your date is coming from aother table you need to add too to the variables 

CIs Test Fixed Table = 
VAR CIs = CALCULATE(
    SUM('Sample IP'[TotalCustomersAffected]),
    REMOVEFILTERS('Sample IP')
)
VAR NonME_CIs = CALCULATE(
    SUM('Sample IP'[nonME_CI]),
    REMOVEFILTERS('Sample IP')
)
VAR MajorEvent = SELECTEDVALUE('Sample IP'[MajorEvent])
VAR MEYes = CIs - NonME_CIs
VAR MENo  = NonME_CIs
RETURN
SWITCH(
    MajorEvent,
    "Y", MEYes,   
    "N", MENo,    
    CIs           
)

If the issue still exists please share your data model with sample data

Thanks 
If you found this helpful, please consider giving it a kudo and marking it as the accepted solution — it goes a long way in helping others facing the same issue.

For more Power BI tips and discussions, let’s connect on LinkedIn:
https://www.linkedin.com/in/natarajan-manivasagan

Cheers!

[anthonjhnon1]

Everything is coming from one table. So I filtered for only two cases 2001838925 and 2046277750 in the small sample data. I should get 17 for 'N' and 4 for 'Y'. However I'm getting 16 and 4. Somehow filtering the case_id is causing this and I don't understand why it would.

[Natarajan_M]

Hi @anthonjhnon1 , Can you check the latest dax i shared ? 

Thanks

[anthonjhnon1]

I tried the [CIs Test Fixed Table] and this basically ignored all filters other than major event because it was re-evaluated after the return statment (I think). I had to study up on variables as constant, something I heard plenty of times, but today it finally clicked.

[Natarajan_M]

@anthonjhnon1 , Sure, if this meets the expected result, then that's great.

Thanks 

If you found this helpful, please consider giving it a kudo and marking it as the accepted solution — it goes a long way in helping others facing the same issue.

For more Power BI tips and discussions, let’s connect on LinkedIn:
https://www.linkedin.com/in/natarajan-manivasagan

Cheers!

[anthonjhnon1]

I'm sorry as I wasn't fully clear. It didn't work. I was getting a value of 6623814 for 'N' and 61087367 for 'Y' even when only filtering for the specific case Ids and the year.

[Natarajan_M]

Hi @anthonjhnon1 , Can you provide a subset of the values with sample data in a table schema format you have , along with the filters you are using on the visuals/page and the expected result? If you can share the PBIX file, it would be helpful for debugging the issue.

Thanks 

[anthonjhnon1]

Files in OneDrive 

Let me know if this works. I couldn't just copy and paste here.

[Natarajan_M]

Hi @anthonjhnon1 , Ideally single flat table modelling is not a good approach so I have taken out the dims and created a proper star schama modelling .

Before : Single fact all calculation refer the same table 

After :

Visual :
N

Y 

Updated PBIX: 

SampleData (1).pbix

Thanks 
If you found this helpful, please consider giving it a kudo and marking it as the accepted solution — it goes a long way in helping others facing the same issue.

For more Power BI tips and discussions, let’s connect on LinkedIn:
https://www.linkedin.com/in/natarajan-manivasagan

Cheers!

[anthonjhnon1]

Thanks for the tip......I'm trying to get MajorEvent = 'N' to equal 173.

[Natarajan_M · Accepted solution]

Hi @anthonjhnon1  , You can update the measuer to use remove filters from the dim table 

CIs Test 2 = 

VAR CIs = CALCULATE(SUM(Fact[TotalCustomersAffected]),REMOVEFILTERS('Major Event'[Major Event]))
VAR NonME_CIs = CALCULATE(SUM(Fact[nonME_CI]),REMOVEFILTERS('Major Event'[Major Event]))
VAR MajorEvent = SELECTEDVALUE('Major Event'[Major Event])

VAR MENo = NonME_CIs
VAR MEYes = CIs - NonME_CIs

RETURN
SWITCH(
    MajorEvent
    , "y", MEYes
    , "n", NonME_CIs
    , CIs
)

SampleData.pbix

Thanks 

[Natarajan_M]

Hi    @anthonjhnon1 
The issue is that the variable is missing the REMOVEFILTERS function, which is necessary to exclude the current selection.
Specifically, for VAR NonME_CIs = SUM('Sample IP'[nonME_CI]), we need to add REMOVEFILTERS on the slicer/filter selection to ensure we calculate the correct value.

CIs Test Fixed = 
VAR CIs = CALCULATE(
    SUM('Sample IP'[TotalCustomersAffected]),
    REMOVEFILTERS('Sample IP'[MajorEvent])
)
VAR NonME_CIs = CALCULATE(
    SUM('Sample IP'[nonME_CI]),
    REMOVEFILTERS('Sample IP'[MajorEvent])
)
VAR MajorEvent = SELECTEDVALUE('Sample IP'[MajorEvent])
VAR MEYes = CIs - NonME_CIs
VAR MENo  = NonME_CIs
RETURN
SWITCH(
    MajorEvent,
    "Y", MEYes,   
    "N", MENo,    
    CIs           
)

Sample data :  

Major event N:

Major event Y:

No selection :

Thanks 

If you found this helpful, please consider giving it a kudo and marking it as the accepted solution — it goes a long way in helping others facing the same issue.

For more Power BI tips and discussions, let’s connect on LinkedIn:
https://www.linkedin.com/in/natarajan-manivasagan

Cheers!

[anthonjhnon1]

I felt like I tried that before. So I created a small sample dataset like yours and it works. However it still doesn't work on my entire dataset. I do have other filters thats only giving the 6 caseID and year of 2026.

However I don't see how that would should affect the measure.