Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

IF AND SUMIF OR

I have the below formula in excel but I need help to write same formular in DAX

=IF(AND(SUMIF(A:A,A2,B:B)

>=10,C2="face"),"Eng"

,IF(AND(SUMIF(A:A,A2,B:B)

>=20,OR(C2="face",C2="online")),"PS"

,IF(AND(D2="pass",E2="CS" ),"Up","Unknown")))

 

Please see sample data below 

 

NamehoursmethodstatuspriorityResult Output
Ade-15facepassCSUp
Ade5facepassCSUp
ade5facepassCSUp
Ade5facepassCSUp
Ade5onlinepasscsUp
Alex5facepasscsEng
Alex5face csEng
Alex5online csPS
Alex5online csPS
Alex5online csPS
Alex5online csPS
Alex5face CS Eng
Chris5facefailwweeeEng
chris5facefailwweeeEng
chris5facefailwweeeEng
Chris5onlinefailwweeePS
chris5onlinefailwweeePS
chris5onlinefailwweeePS
Chris5onlinefailwweeePS
chris5facefailwweeeEng
chris5facefailwweeeEng
Ola5facefailwweeeEng
ola5facefailwweeeEng
Tobi5onlinefailwweeePS
tobi5facefailwweeeEng
tobi10facefailwweeeEng

 

Thanks

Need Help
  • Ashish_Mathur's avatar
    Ashish_Mathur
    6 years ago

    Hi,

    This calculated column formula works

    =if(AND(CALCULATE(SUM(Data[hours]),FILTER(Data,Data[Name]=EARLIER(Data[Name])))>=10,Data[method]="face"),"Eng",IF(AND(CALCULATE(SUM(Data[hours]),FILTER(Data,Data[Name]=EARLIER(Data[Name])))>=20,OR(Data[method]="online",Data[method]="face")),"PS",IF(AND(Data[status]="pass",Data[priority]="CS"),"Up","Unknown")))

    Hope this helps.

  • Greg_Deckler's avatar
    Greg_Deckler
    6 years ago

    Anonymous - I did find one slight error in Anonymous's formula so I corrected it and did some formatting. PBIX is attached.

     

    Result Output = 
    VAR __Sum = SUMX(FILTER(ALL('Table'),[Name]=EARLIER([Name])),[hours])
RETURN
    SWITCH(
        TRUE(),
        __Sum >= 10 && [method]="face","Eng",
        __Sum >= 20 && ([method]="face" || [method]="online"),"PS",
        [status] = "pass" && [priority] = "CS","Up",
        "Unknown"
    )

19 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Hi Anonymous ,

     

    You can use a calculated measure. I've broken up the syntax to match your calculated IF statement. 

     

    Your IF Statement:

    =IF(AND(SUMIF(A:A,A2,B:B)>=10,C2="face"),"Eng"

    ,IF(AND(SUMIF(A:A,A2,B:B)>=20,OR(C2="face",C2="online")),"PS"

    ,IF(AND(D2="pass",E2="CS" ),"Up"
    ,"Unknown")))

     


    Result =
    Var Val_Sum = calculate(sum(hours))
    Return

    switch(true(),Val_Sum>=10&&[method]="face","Eng"
    ,Val_Sum>=20&&(or([method]="face",[method]="online"),"PS"
    ,[status]="pass"&&[priority]="CS","Up"
    ,"Unknown")

     

    OR if your results are in a table format with that level of granularity, you could do a calculated column.

     

    Result =
    Var Val_Sum = [hours]
    Return

    switch(true(),Val_Sum>=10&&[method]="face","Eng"
    ,Val_Sum>=20&&(or([method]="face",[method]="online"),"PS"
    ,[status]="pass"&&[priority]="CS","Up"
    ,"Unknown")

     

     

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    6 years ago

    OK, the replacement for SUMIF is to use SUMX with a FILTER or wrap a SUM with CALCULATE and a filter. IF, AND and OR are all the same as excel. However, I would use a SWITCH TRUE statement because nested IF statements give me the willys. I don't speak much Excel and I don't want to go look up SUMIF so what is the SUMIF doing? Can you explain your Excel formula in non-code?

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Please see what am trying to archieve in none code format :

       

      Eng = must have at least 10 hours and status = FACE
       
      PS=Hours = At Least 20 hours and status = Face OR online
       
      UP: Status = Pass and Priority = CS Accelerator .
       
      I hope this helps Greg_Deckler 
      • Greg_Deckler's avatar
        Greg_Deckler
        Community Champion
        6 years ago

        Sure, let me know if Anonymous 's solution didn't work for you, looks solid to me.

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    6 years ago

    Hi,

    This calculated column formula works

    =if(AND(CALCULATE(SUM(Data[hours]),FILTER(Data,Data[Name]=EARLIER(Data[Name])))>=10,Data[method]="face"),"Eng",IF(AND(CALCULATE(SUM(Data[hours]),FILTER(Data,Data[Name]=EARLIER(Data[Name])))>=20,OR(Data[method]="online",Data[method]="face")),"PS",IF(AND(Data[status]="pass",Data[priority]="CS"),"Up","Unknown")))

    Hope this helps.

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Ashish_Mathur Greg_Deckler 

       

      I would like to amend this query to :

       

      Eng = must have at least 10 hours and status = FACE, i.e 

       

      =IF(AND(CALCULATE(SUM(Data[hours]),FILTER(Data,Data[Name]=EARLIER(Data[Name])))>=10,Data[method]="face"),"Eng",I,"Unknown")