cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
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

Accepted Solutions
Highlighted
Super User IV
Super User IV

Re: 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.

Untitled.png


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

View solution in original post

Highlighted
Super User IV
Super User IV

Re: IF AND SUMIF OR

@moeconsult - I did find one slight error in @paulq'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"
    )

---------------------------------------

Putting square pegs in round holes since 1972.

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

19 REPLIES 19
Highlighted
Super User IV
Super User IV

Re: IF AND SUMIF OR

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?


---------------------------------------

Putting square pegs in round holes since 1972.

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Highlighted
Helper I
Helper I

Re: IF AND SUMIF OR

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

 

 

Highlighted
Helper V
Helper V

Re: IF AND SUMIF OR

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 
Highlighted
Super User IV
Super User IV

Re: IF AND SUMIF OR

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


---------------------------------------

Putting square pegs in round holes since 1972.

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Highlighted
Super User IV
Super User IV

Re: 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.

Untitled.png


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

View solution in original post

Highlighted
Super User IV
Super User IV

Re: IF AND SUMIF OR

@moeconsult - I did find one slight error in @paulq'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"
    )

---------------------------------------

Putting square pegs in round holes since 1972.

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

Highlighted
Helper V
Helper V

DisticntCount

@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 

Highlighted
Super User IV
Super User IV

Re: DisticntCount

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/
Highlighted
Helper V
Helper V

Re: DisticntCount

I want disticnt count of ID

Helpful resources

Announcements
May 2020 Community Highlights

May 2020 Community Highlights

It’s time for another PBI Community recap!

Community Blog

Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

Using the Community

Using the Community

Need help with the Power BI Community? Our 'Using the Community' support articles are a great place to start.

Galleries

Galleries

Looking for inspiration on how to present your data? Need instructional videos? Check out our Galleries!

Top Solution Authors