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"
    )
Ashish_Mathur's avatar
Ashish_Mathur
Super User
6 years ago

Hi,

Write this measure

=Distinctcount(Data[ID])

Hope this helps.

Anonymous's avatar
Anonymous
Not applicable
6 years ago

That was the measure I created but the total was not adding up