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

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 

    • Ashish_Mathur's avatar
      Ashish_Mathur
      Super User
      6 years ago

      Hi,

      Your question is not clear.  What exact result do you want?

      • Anonymous's avatar
        Anonymous
        Not applicable
        6 years ago

        I want disticnt count of ID

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Please I need help with the excel formula in Dax.

    Sample data and excel formula below 

     

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

     

     A            B               C                D

    NamehoursmethodOutput
    Ade-15faceUnknown
    Tobi5onlineUnknown
    Ade5faceUnknown
    Ola5faceEngaged
    tobi10faceEngaged
    ade5faceUnknown
    tobi10faceEngaged
    ola5faceEngaged
    Ade5faceUnknown
    Ade5onlineUnknown
    Chris5faceEngaged
    chris5faceEngaged
    chris5faceEngaged
    Chris5onlineUnknown
    chris5onlineUnknown
    chris5onlineUnknown
    Chris5onlineUnknown
    chris5faceEngaged
    chris5faceEngaged
    Alex5faceEngaged
    Alex5faceEngaged
    Alex5onlineUnknown
    Alex5onlineUnknown
    Alex5onlineUnknown
    Alex5onlineUnknown
    Alex5faceEngaged
        

     

    Greg_Deckler Ashish_Mathur Anonymous 

    • Ashish_Mathur's avatar
      Ashish_Mathur
      Super User
      6 years ago

      Hi,

      Try this calculated column formula

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

      Hope this helps.