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...
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"
    )
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.

    • Greg_Deckler's avatar
      Greg_Deckler
      Community Champion
      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"
    )
      • Anonymous's avatar
        Anonymous
        Not applicable
        6 years ago

        Greg_Deckler  Thanks for your help, I created a matrix with the result of the calculated column and DISTINCTCOUNT(id) for my measure as I want to distinct count of the id but the total is not suming up. I have updated my table with a new column "ID" . And also attached pictures of my table and matrix visual .

         

        culated column and 

         

        NamehoursmethodstatuspriorityResult Output ID
        Ade-15facepassCSUp 57YE43YGD
        Ade5facepassCSUp 57YE43YGD
        ade5facepassCSUp 57YE43YGD
        Ade5facepassCSUp 57YE43YGD
        Ade5onlinepasscsUp 57YE43YGD
        Alex5facepasscsEng 87HBDGD
        Alex5face csEng 87HBDGD
        Alex5online csPS 87HBDGD
        Alex5online csPS 87HBDGD
        Alex5online csPS 87HBDGD
        Alex5online csPS 87HBDGD
        Alex5face CS Eng 87HBDGD
        Chris5facefailwweeeEng OU2809
        chris5facefailwweeeEng OU2809
        chris5facefailwweeeEng OU2809
        Chris5onlinefailwweeePS OU2809
        chris5onlinefailwweeePS OU2809
        chris5onlinefailwweeePS OU2809
        Chris5onlinefailwweeePS OU2809
        chris5facefailwweeeEng OU2809
        chris5facefailwweeeEng OU2809
        Ola5facefailwweeeEng URH879
        ola5facefailwweeeEng URH879
        Tobi5onlinefailwweeePS HGT153
        tobi5facefailwweeeEng HGT153
        tobi10facefailwweeeEng HGT153

         

        Ashish_Mathur Greg_Deckler