Forum Discussion

M_SBS_6's avatar
M_SBS_6
Helper V
3 years ago
Solved

SUM using IF, AND, OR

Hi, I am trying to work out what the correct DAX would be required to create the following measure, any suggestions please? 

 

If(

apps[apps_status] = "live"

&&

(month_index[is_prior_months]) = "yes" then 0

else

If(apps[apps_status] = "test"

&&

(month_index[is_prior_months]) = "yes",

or

If(apps[apps_status = "test"

&& apps[app_value] <30 

then 0

else  

sum(apps[amount])

Need Help
  • Greg_Deckler's avatar
    Greg_Deckler
    3 years ago

    M_SBS_6 Maybe:

    Measure = 
  VAR __AppStatus = MAX('apps'[app_status])
  VAR __IsPriorMonth = MAX('month_index'[is_prior_month])
  VAR __AppValue = MAX('apps'[app_value])
  VAR __Result =
    SWITCH( TRUE(),
      __AppStatus = "live" && __IsPriorMonth = "yes", 0,
      ( __AppStatus = "test" && __IsPriorMonth = "yes" ) ||
        ( __AppStatus = "test" && __AppValue < 30 ), 0,
      SUM('apps'[amount])
    )
RETURN
  __Result

2 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    3 years ago

    M_SBS_6 Maybe:

    Measure = 
  VAR __AppStatus = MAX('apps'[app_status])
  VAR __IsPriorMonth = MAX('month_index'[is_prior_month])
  VAR __AppValue = MAX('apps'[app_value])
  VAR __Result =
    SWITCH( TRUE(),
      __AppStatus = "live" && __IsPriorMonth = "yes", 0,
      ( __AppStatus = "test" && __IsPriorMonth = "yes" ) ||
        ( __AppStatus = "test" && __AppValue < 30 ), 0,
      SUM('apps'[amount])
    )
RETURN
  __Result
    • M_SBS_6's avatar
      M_SBS_6
      Helper V
      3 years ago

      Thanks for your help on this Greg, hugely appreciated.