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 



cengizhanarslan's avatar
cengizhanarslan
Super User
5 months ago

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
    )