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

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!

    • anthonjhnon1's avatar
      anthonjhnon1
      Regular Visitor
      5 months ago

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

        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