Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
moeconsult
Helper V
Helper V

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(AND(D2="pass",E2="CS" ),"Up","Unknown")))

 

Please see sample data below 

 

NamehoursmethodstatuspriorityResult Output
Ade-15facepassCSUp
Ade5facepassCSUp
ade5facepassCSUp
Ade5facepassCSUp
Ade5onlinepasscsUp
Alex5facepasscsEng
Alex5face csEng
Alex5online csPS
Alex5online csPS
Alex5online csPS
Alex5online csPS
Alex5face CS Eng
Chris5facefailwweeeEng
chris5facefailwweeeEng
chris5facefailwweeeEng
Chris5onlinefailwweeePS
chris5onlinefailwweeePS
chris5onlinefailwweeePS
Chris5onlinefailwweeePS
chris5facefailwweeeEng
chris5facefailwweeeEng
Ola5facefailwweeeEng
ola5facefailwweeeEng
Tobi5onlinefailwweeePS
tobi5facefailwweeeEng
tobi10facefailwweeeEng

 

Thanks

2 ACCEPTED SOLUTIONS
Ashish_Mathur
Super User
Super User

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.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

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

@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

View solution in original post

19 REPLIES 19
Ashish_Mathur
Super User
Super User

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.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

@Ashish_Mathur @Greg_Deckler 

 

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

Anonymous
Not applicable

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

 

 

Greg_Deckler
Super User
Super User

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?


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

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 

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

@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

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 

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.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

@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 .

 

WhatsApp Image 2020-03-01 at 21.30.25 (1).jpegWhatsApp Image 2020-03-01 at 21.30.25.jpegculated 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 

Hi,

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


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

@Ashish_Mathur  What I want is disticnt coung of ID

Hi,

Write this measure

=Distinctcount(Data[ID])

Hope this helps.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

@Ashish_Mathur 

 

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 SchoolNENorth JamPrimary02/03/202010090Attended 
Bronte SchoolNENorth JamPrimary18/03/202010090Attended 
Bronte SchoolNENorth JamPrimary19/03/202010070Attended 

See sample data and Result column:

OrganisationRegionSPPhasedateattendanceStatusResult Column     
Bronte SchoolNENorth JamPrimary02/03/202010090Attended1
Bronte SchoolNENorth JamPrimary18/03/202010090Attended 
Bronte SchoolNENorth JamPrimary19/03/202010070Pending 
Ola SchoolNWNorth WestSecondary19/03/201910090Attended1
Ola SchoolNWNorth WestSecondary20/03/201910090Attended 
Ola SchoolNWNorth WestSecondary22/03/201910080Absent 
Help CollegeNENorth Westothers05/08/202010090Attended0
Help CollegeNENorth Westothers10/08/201910090Attended1
Help CollegeNENorth Westothers01/05/202010090Attended0
Bronte SchoolNENorth JamPrimary02/03/202010091Cancelled 

 

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:

Sample.JPG

 

Visual sample when Attended is selected in the slicers show 2 for some orga and zero when they should be 1

Attended.jpeg

 

Visual sample when cancelled is selected in the slicer and returning zeros but I number of cancelled sessions 

Cancle.jpeg

Please let me know if you require more information @Ashish_Mathur 

Hi,

I am still not clear.  Someone else will help you.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

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


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Hi,

I cannot understand.  I will need to see the file.  Share the download link and tell me exactly where the problem is.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

I want disticnt count of ID

Sure, let me know if @Anonymous 's solution didn't work for you, looks solid to me.


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.