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

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

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

    Thanks

    • anthonjhnon1's avatar
      anthonjhnon1
      Regular Visitor
      5 months ago

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

        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!