DAX equivalent for sumif

[ottster]

Hi

In Excel I'd use a sumif function to add 2 columns and if they added to 2 to return a 1 otherwise a 0. I've tried every which way to do this using DAX but it's always the wrong answer compared to excel - how do i do?

example

In excel to create the column called call 1 and call 2 i'd use this formula: =IF(SUM(FL2:FM2)=2,1,0), how can i replicate this in DAX

call 1	call 2	call 1 and call 2
1	1	1
1	1	1
1	0	0
1	0	0
1	1	1
1	0	0
1	1	1
1	0	0
1	0	0
1	0	0
1	0	0
1	1	1

[SamsonTruong · Accepted solution]

Hi @ottster ,

To replicate this in DAX, you can use the folllowing DAX calculated column:

DAX = 
IF(
    'YourTable'[Call 1] + 'YourTable'[Call 2] = 2,
    1,
    0
)

If this helped, please mark it as the solution so others can benefit too. And if you found it useful, kudos are always appreciated.

Thanks,

Samson

[Nasif_Azam · Accepted solution]

Hey @ottster ,

To replicate your Excel formula =IF(SUM(FL2:FM2)=2,1,0) in DAX, you’ll use a calculated column that checks the sum of two columns (e.g., [call 1] and [call 2]) and returns 1 if the sum equals 2, else 0.

DAX

Assuming your columns are named [call 1] and [call 2], use this formula in a new calculated column:

call 1 and call 2 = 
IF([call 1] + [call 2] = 2, 
   1, 
   0)

This works exactly like your Excel logic:

• It adds the values in [call 1] and [call 2] for each row.

• If the result is 2, it returns 1.

• Otherwise, it returns 0.

How to Add This in Power BI:

1. Go to your table in Data view.

2. Click "New Column".

3. Paste the formula shown above.

4. Rename the column if needed.

If you found this solution helpful, please consider accepting it and giving it a kudos (Like) it’s greatly appreciated and helps others find the solution more easily.

Best Regards,
Nasif Azam

[maruthisp · Accepted solution]

Hi ottster,

Based on your example, you may use below DAX expression to solve your problem:
Call1AndCall2 =
IF(
'CallData'[Call1] + 'CallData'[Call2] = 2,
1,
0
)

Hope the above DAX expression may help you.

Please let me know if you have further questions.

If this reply helped solve your problem, please consider clicking "Accept as Solution" so others can benefit too. And if you found it useful, a quick "Kudos" is always appreciated, thanks! 

Best Regards, 

Maruthi 

LinkedIn - http://www.linkedin.com/in/maruthi-siva-prasad/ 

X            -  Maruthi Siva Prasad - (@MaruthiSP) / X

[ottster · Accepted solution]

Thanks all for coming back to me, i did try this yesterday and the answer i got was the same which i thought was wrong - turns out i needed to split the table and add a filter on for the data which overlaps. DOH! 

Thanks @maruthisp @Nasif_Azam @SamsonTruong I shall remember you all for me next problem!

[SamsonTruong]

Glad to hear a solution was found!

[v-mdharahman]

Hi @ottster,

Thanks for reaching out to the Microsoft fabric community forum.

It looks like you want to add two columns into one single column and if this successfully happens then return '1' or else return '0'. As @maruthisp, @Nasif_Azam and @SamsonTruong all have already responded to your query, kindly go through their responses and check if it answers your requirements. And if it does then kindly mark the helpful reply as solution so that other commuity members with similar issue can find the solution easily.

I would also take a moment to thank @maruthisp, @Nasif_Azam and @SamsonTruong, for actively participating in the community forum and for the solutions you’ve been sharing in the community forum. Your contributions make a real difference.

If I misunderstand your needs or you still have problems on it, please feel free to let us know.  

Best Regards,
Hammad.
Community Support Team

If this post helps then please mark it as a solution, so that other members find it more quickly.

Thank you.

[maruthisp · Accepted solution]

Hi ottster,

Based on your example, you may use below DAX expression to solve your problem:
Call1AndCall2 =
IF(
'CallData'[Call1] + 'CallData'[Call2] = 2,
1,
0
)

Hope the above DAX expression may help you.

Please let me know if you have further questions.

If this reply helped solve your problem, please consider clicking "Accept as Solution" so others can benefit too. And if you found it useful, a quick "Kudos" is always appreciated, thanks! 

Best Regards, 

Maruthi 

LinkedIn - http://www.linkedin.com/in/maruthi-siva-prasad/ 

X            -  Maruthi Siva Prasad - (@MaruthiSP) / X

[Nasif_Azam · Accepted solution]

Hey @ottster ,

To replicate your Excel formula =IF(SUM(FL2:FM2)=2,1,0) in DAX, you’ll use a calculated column that checks the sum of two columns (e.g., [call 1] and [call 2]) and returns 1 if the sum equals 2, else 0.

DAX

Assuming your columns are named [call 1] and [call 2], use this formula in a new calculated column:

call 1 and call 2 = 
IF([call 1] + [call 2] = 2, 
   1, 
   0)

This works exactly like your Excel logic:

• It adds the values in [call 1] and [call 2] for each row.

• If the result is 2, it returns 1.

• Otherwise, it returns 0.

How to Add This in Power BI:

1. Go to your table in Data view.

2. Click "New Column".

3. Paste the formula shown above.

4. Rename the column if needed.

If you found this solution helpful, please consider accepting it and giving it a kudos (Like) it’s greatly appreciated and helps others find the solution more easily.

Best Regards,
Nasif Azam

[SamsonTruong · Accepted solution]

Hi @ottster ,

To replicate this in DAX, you can use the folllowing DAX calculated column:

DAX = 
IF(
    'YourTable'[Call 1] + 'YourTable'[Call 2] = 2,
    1,
    0
)

If this helped, please mark it as the solution so others can benefit too. And if you found it useful, kudos are always appreciated.

Thanks,

Samson