cancel
Showing results for
Did you mean:
Frequent Visitor

Convert Excel logic to DAX

Hi Datafam.

I want to convert my excel formula "=IF(OR(AB6<>0,AL6="A"),SUMPRODUCT(--(\$E\$6:\$E\$5160=E6),--(\$G\$6:\$G\$5160=G6),--(\$M\$6:\$M\$5160=M6),--(\$AK\$6:\$AK\$5160<>"E"),\$Y\$6:\$Y\$5160),0)" into DAX.

Thanks,
Pratik.S

1 ACCEPTED SOLUTION
Community Support

Hi @UI

"=IF(OR(AB6<>0,AL6="A"),SUMPRODUCT(--(\$E\$6:\$E\$5160=E6),--(\$G\$6:\$G\$5160=G6),--(\$M\$6:\$M\$5160=M6),--(\$AK\$6:\$AK\$5160<>"E"),\$Y\$6:\$Y\$5160),0)"

you can try this, create a column

``````column=

var _expression_true= SUMX(<table>, <expression>) // This corresponds to SUMPRODUCT()

var _expression_false=0

return

if( table[column AB6]<>0 ||  table[column AL6]="A", _expression_true,_expression_false)``````

in excel you could sum up individual cells but in power bi, when you use a sum function it's summing up the entire column. So when doing summation calculation, we need to filter the table.

since not sure what are you trying to sum up, could you share some sample data? then we can work on it further.

Also, please watch this video, it will help starters to understand DAX.

📊 How to use Power BI DAX - Tutorial - YouTube

Best Regards,

Community Support Team _Tang

If this post helps, please consider Accept it as the solution to help the other members find it more quickly.

2 REPLIES 2
Community Support

Hi @UI

"=IF(OR(AB6<>0,AL6="A"),SUMPRODUCT(--(\$E\$6:\$E\$5160=E6),--(\$G\$6:\$G\$5160=G6),--(\$M\$6:\$M\$5160=M6),--(\$AK\$6:\$AK\$5160<>"E"),\$Y\$6:\$Y\$5160),0)"

you can try this, create a column

``````column=

var _expression_true= SUMX(<table>, <expression>) // This corresponds to SUMPRODUCT()

var _expression_false=0

return

if( table[column AB6]<>0 ||  table[column AL6]="A", _expression_true,_expression_false)``````

in excel you could sum up individual cells but in power bi, when you use a sum function it's summing up the entire column. So when doing summation calculation, we need to filter the table.

since not sure what are you trying to sum up, could you share some sample data? then we can work on it further.

Also, please watch this video, it will help starters to understand DAX.

📊 How to use Power BI DAX - Tutorial - YouTube

Best Regards,

Community Support Team _Tang

If this post helps, please consider Accept it as the solution to help the other members find it more quickly.

Super User

@UI , Can you share what calculation is needed

You can sumx or productX

New Power BI Features
Datamarts: https://youtu.be/8tskWsJTEpg
Field Parameters : https://youtu.be/lqF3Wa1FllE?t=70
Dashboard of My Blogs !! Connect on Linkedin !! Proud to be a Super User!
Want To Learn Power BI
Learn Power BI Beginners !! Advance Power BI Concepts !! Power BI For Tableau User !! Learn Power BI in Hindi !!
!! Subscribe to my youtube Channel !!

Announcements