Forum Discussion
IF AND SUMIF OR
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 - 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" )
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")