Forum Discussion
Converting excel formulars to DAX
Hi
Not quire sure, if this is the right sub category to ask this question but: Could you help med translate this Excel formular into DAX:
=if(and(B3>=B$1;C3>=C$1);"HERO";if(and(B3<=B$1;C3<=C$1);"HORROR";if(and(B3>=B$1;C3<=C$1);"HYPE";if(and(B3<=B$1;C3>=C$1);"HIDDEN";0)))
Hope any of you can help me out. I'm gonna watch the DAX learning module right away.
Hi Anonymous ,
As djerro123 said, DAX in Power BI is for columns rather than cells. Based on your formula, I create a simple sample that you can reference.
Column = VAR A2 = CALCULATE(SUM('Table'[Column2]),FILTER('Table','Table'[Column1] = "A")) VAR A3 = CALCULATE(SUM('Table'[Column3]),FILTER('Table','Table'[Column1] = "A")) VAR C2 = CALCULATE(SUM('Table'[Column2]),FILTER('Table','Table'[Column1] = "C")) VAR C3 = CALCULATE(SUM('Table'[Column3]),FILTER('Table','Table'[Column1] = "C")) RETURN IF ( AND ( C2 >= A2, C3 >= A3 ),"HERO", IF ( AND ( C2 <= A2, C3 <= A3 ),"HORROR", IF ( AND ( C2 >= A2, C3 <= A3 ),"HYPE", IF ( AND ( C2 <= A2, C3 >= A3 ), "HIDDEN", "0" ) ) ) )There are some blogs about DAX in Power BI Desktop that you could learn from.
How to correctly use IF in Power BI DAX
DAX basics in Power BI Desktop
https://docs.microsoft.com/en-us/power-bi/guided-learning/introductiontodax?tutorial-step=6
Best Regards,
Xue Ding
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
4 Replies
- JarroVGIT
Resident Rockstar
I am afraid that DAX doesn't work like that. The big difference between Excel formula's and DAX in PowerBI is that the Excel formula results in 1 value, in one particular cell. DAX in PowerBI evaluates all kind of things and it's output can be a column, a table, a single value, etc. So, translating your formula just isn't possible.
However, there are similarities. IF(<expression>, <value if true>, <value if false>) is valid in Power BI DAX. The AND(<expression>, <expression>) does also exist in PowerBI DAX, albeit limited to two expressions only. You can circumvent this by using the && operator. (<expression> && <expression) is the same as AND(<expression>, <expression>), but is expandable with more expressions. The same goes for the OR operator ( || ).
Kind regards
Djerro123
-------------------------------
If this answered your question, please mark it as the Solution. This also helps others to find what they are looking for.
Kudo's are welcome 🙂
v-xuding-msftCommunity Support
Hi Anonymous ,
As djerro123 said, DAX in Power BI is for columns rather than cells. Based on your formula, I create a simple sample that you can reference.
Column = VAR A2 = CALCULATE(SUM('Table'[Column2]),FILTER('Table','Table'[Column1] = "A")) VAR A3 = CALCULATE(SUM('Table'[Column3]),FILTER('Table','Table'[Column1] = "A")) VAR C2 = CALCULATE(SUM('Table'[Column2]),FILTER('Table','Table'[Column1] = "C")) VAR C3 = CALCULATE(SUM('Table'[Column3]),FILTER('Table','Table'[Column1] = "C")) RETURN IF ( AND ( C2 >= A2, C3 >= A3 ),"HERO", IF ( AND ( C2 <= A2, C3 <= A3 ),"HORROR", IF ( AND ( C2 >= A2, C3 <= A3 ),"HYPE", IF ( AND ( C2 <= A2, C3 >= A3 ), "HIDDEN", "0" ) ) ) )There are some blogs about DAX in Power BI Desktop that you could learn from.
How to correctly use IF in Power BI DAX
DAX basics in Power BI Desktop
https://docs.microsoft.com/en-us/power-bi/guided-learning/introductiontodax?tutorial-step=6
Best Regards,
Xue Ding
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
- v-xuding-msft
Hi Anonymous ,
Wish you will be familiar with DAX soon. If you have any problem, please feel free to ask us in the Power BI forum.
Best Regards,
Xue Ding
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. Kudos are nice too.
