Forum Discussion

anthonjhnon1's avatar
anthonjhnon1
Regular Visitor
5 months ago
Solved

DAX Formula Help

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 ...
"DAX help" 'Need help'
  • Natarajan_M's avatar
    Natarajan_M
    5 months ago

    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 



anthonjhnon1's avatar
anthonjhnon1
Regular Visitor
5 months ago

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's avatar
    Natarajan_M
    Super User
    5 months ago

    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's avatar
      anthonjhnon1
      Regular Visitor
      5 months ago

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