Forum Discussion

Anonymous

Not applicable

6 years ago

Solved

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

6 years ago

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.

Icon for Resident Rockstar rank

Resident Rockstar

6 years ago

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 🙂