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 



Natarajan_M's avatar
Natarajan_M
Super User
5 months ago

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

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.