Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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"
)
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.
I would like to amend this query to :
Eng = must have at least 10 hours and status = FACE, i.e
=IF(AND(CALCULATE(SUM(Data[hours]),FILTER(Data,Data[Name]=EARLIER(Data[Name])))>=10,Data[method]="face"),"Eng",I,"Unknown")
Hi @moeconsult ,
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")
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?
Please see what am trying to archieve in none code format :
@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"
)
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
Name | hours | method | Output |
Ade | -15 | face | Unknown |
Tobi | 5 | online | Unknown |
Ade | 5 | face | Unknown |
Ola | 5 | face | Engaged |
tobi | 10 | face | Engaged |
ade | 5 | face | Unknown |
tobi | 10 | face | Engaged |
ola | 5 | face | Engaged |
Ade | 5 | face | Unknown |
Ade | 5 | online | Unknown |
Chris | 5 | face | Engaged |
chris | 5 | face | Engaged |
chris | 5 | face | Engaged |
Chris | 5 | online | Unknown |
chris | 5 | online | Unknown |
chris | 5 | online | Unknown |
Chris | 5 | online | Unknown |
chris | 5 | face | Engaged |
chris | 5 | face | Engaged |
Alex | 5 | face | Engaged |
Alex | 5 | face | Engaged |
Alex | 5 | online | Unknown |
Alex | 5 | online | Unknown |
Alex | 5 | online | Unknown |
Alex | 5 | online | Unknown |
Alex | 5 | face | Engaged |
@Greg_Deckler @Ashish_Mathur @Anonymous
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.
@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
Name | hours | method | status | priority | Result Output | ID | |
Ade | -15 | face | pass | CS | Up | 57YE43YGD | |
Ade | 5 | face | pass | CS | Up | 57YE43YGD | |
ade | 5 | face | pass | CS | Up | 57YE43YGD | |
Ade | 5 | face | pass | CS | Up | 57YE43YGD | |
Ade | 5 | online | pass | cs | Up | 57YE43YGD | |
Alex | 5 | face | pass | cs | Eng | 87HBDGD | |
Alex | 5 | face | cs | Eng | 87HBDGD | ||
Alex | 5 | online | cs | PS | 87HBDGD | ||
Alex | 5 | online | cs | PS | 87HBDGD | ||
Alex | 5 | online | cs | PS | 87HBDGD | ||
Alex | 5 | online | cs | PS | 87HBDGD | ||
Alex | 5 | face | CS | Eng | 87HBDGD | ||
Chris | 5 | face | fail | wweee | Eng | OU2809 | |
chris | 5 | face | fail | wweee | Eng | OU2809 | |
chris | 5 | face | fail | wweee | Eng | OU2809 | |
Chris | 5 | online | fail | wweee | PS | OU2809 | |
chris | 5 | online | fail | wweee | PS | OU2809 | |
chris | 5 | online | fail | wweee | PS | OU2809 | |
Chris | 5 | online | fail | wweee | PS | OU2809 | |
chris | 5 | face | fail | wweee | Eng | OU2809 | |
chris | 5 | face | fail | wweee | Eng | OU2809 | |
Ola | 5 | face | fail | wweee | Eng | URH879 | |
ola | 5 | face | fail | wweee | Eng | URH879 | |
Tobi | 5 | online | fail | wweee | PS | HGT153 | |
tobi | 5 | face | fail | wweee | Eng | HGT153 | |
tobi | 10 | face | fail | wweee | Eng | HGT153 |
Hi,
Your question is not clear. What exact result do you want?
Hi,
Write this measure
=Distinctcount(Data[ID])
Hope this helps.
am trying to archive is first attendance occurence. Example below, bronte school attended 3 different sessions but I want to the count of their earliest attendance and still able to see sum of other status when selected in the slicer
Bronte School | NE | North Jam | Primary | 02/03/2020 | 10090 | Attended | |
Bronte School | NE | North Jam | Primary | 18/03/2020 | 10090 | Attended | |
Bronte School | NE | North Jam | Primary | 19/03/2020 | 10070 | Attended |
See sample data and Result column:
Organisation | Region | SP | Phase | date | attendance | Status | Result Column |
Bronte School | NE | North Jam | Primary | 02/03/2020 | 10090 | Attended | 1 |
Bronte School | NE | North Jam | Primary | 18/03/2020 | 10090 | Attended | |
Bronte School | NE | North Jam | Primary | 19/03/2020 | 10070 | Pending | |
Ola School | NW | North West | Secondary | 19/03/2019 | 10090 | Attended | 1 |
Ola School | NW | North West | Secondary | 20/03/2019 | 10090 | Attended | |
Ola School | NW | North West | Secondary | 22/03/2019 | 10080 | Absent | |
Help College | NE | North West | others | 05/08/2020 | 10090 | Attended | 0 |
Help College | NE | North West | others | 10/08/2019 | 10090 | Attended | 1 |
Help College | NE | North West | others | 01/05/2020 | 10090 | Attended | 0 |
Bronte School | NE | North Jam | Primary | 02/03/2020 | 10091 | Cancelled |
I have used the formula below but does seems to be giving me what I want, as its returning zero some attended status and 2 count in some occassions .
Result calculated = if(Sheet1[date]=minx(filter(Sheet1,Sheet1[Organisation]=EARLIER(Sheet1[Organisation]) && Sheet1[Status]="Attended"),Sheet1[date]) && Sheet1[Status] = "Attended",1,0)
I also want to be able to return values of other status [Cancelled,Absent,Pending] when select them in my slicer
Visual Sample:
Visual sample when Attended is selected in the slicers show 2 for some orga and zero when they should be 1
Visual sample when cancelled is selected in the slicer and returning zeros but I number of cancelled sessions
Please let me know if you require more information @Ashish_Mathur
Hi,
I am still not clear. Someone else will help you.
That was the measure I created but the total was not adding up
@moeconsult - What you have is a measure totals problem. Very common. See my post about it here: https://community.powerbi.com/t5/DAX-Commands-and-Tips/Dealing-with-Measure-Totals/td-p/63376
Also, this Quick Measure, Measure Totals, The Final Word should get you what you need:
https://community.powerbi.com/t5/Quick-Measures-Gallery/Measure-Totals-The-Final-Word/m-p/547907
Hi,
I cannot understand. I will need to see the file. Share the download link and tell me exactly where the problem is.
I want disticnt count of ID
Sure, let me know if @Anonymous 's solution didn't work for you, looks solid to me.
User | Count |
---|---|
102 | |
91 | |
87 | |
79 | |
71 |
User | Count |
---|---|
113 | |
105 | |
101 | |
75 | |
64 |