Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Name | hours | method | status | priority | Result Output |
Ade | -15 | face | pass | CS | Up |
Ade | 5 | face | pass | CS | Up |
ade | 5 | face | pass | CS | Up |
Ade | 5 | face | pass | CS | Up |
Ade | 5 | online | pass | cs | Up |
Alex | 5 | face | pass | cs | Eng |
Alex | 5 | face | cs | Eng | |
Alex | 5 | online | cs | PS | |
Alex | 5 | online | cs | PS | |
Alex | 5 | online | cs | PS | |
Alex | 5 | online | cs | PS | |
Alex | 5 | face | CS | Eng | |
Chris | 5 | face | fail | wweee | Eng |
chris | 5 | face | fail | wweee | Eng |
chris | 5 | face | fail | wweee | Eng |
Chris | 5 | online | fail | wweee | PS |
chris | 5 | online | fail | wweee | PS |
chris | 5 | online | fail | wweee | PS |
Chris | 5 | online | fail | wweee | PS |
chris | 5 | face | fail | wweee | Eng |
chris | 5 | face | fail | wweee | Eng |
Ola | 5 | face | fail | wweee | Eng |
ola | 5 | face | fail | wweee | Eng |
Tobi | 5 | online | fail | wweee | PS |
tobi | 5 | face | fail | wweee | Eng |
tobi | 10 | face | fail | wweee | Eng |
Thanks
Solved! Go to Solution.
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.
@moeconsult - 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"
)
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
113 | |
97 | |
80 | |
69 | |
59 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |