Nested IF functions in DAX power bi

[Haidarius]

So I have this logic from crystal reports formula builder that I'd like to follow into powerBI's DAX and create a new column with the result:

if {tableA.Col1} = "PAY" and {tableB.Col#} in ["LEG", "HAND"] and {tableA.Col2} = "Y" then "N" else "Y"

I cant seem to create any logical statement to refer to another table within DAX (up to my knowledge) or actually nest multiple ifs like above, here is what I have so far..

Error = IF(tableA[col1] = "PAY" && IF(tableB[col#] = "LEG"&&"HAND" && IF(tableA[col2] = "Y","N","Y"),"Y"),"Y")

[ValtteriN]

Hi,

I am not sure I understood the issue but here are some general pointers:

For nested ifs I would use SWITCH + TRUE structure. E.g.
SWITCH(TRUE(),
condition 1, result 1,
condition 2, result 2...)

If you want to use multiple tables in one expression make sure your realtionships are functional and use expressions such as RELATED.

[tamerj1]

Hi @Haidarius 

please try

=
IF (
    SELECTEDVALUE ( tableA[Col1] ) = "PAY"
        && SELECTEDVALUE ( tableB[Col#] )
            IN { "LEG", "HAND" }
                && SELECTEDVALUE ( tableA[Col2] ) = "Y",
    "N",
    "Y"
)

[johnt75 · Accepted solution]

If tableB is on the one side of a relationship with table A then you can use the RELATED function. 

The table constructor in DAX is { }, so you could create your new column like

Error Column =
IF (
    tableA[col1] = "PAY"
        && RELATED ( tableB[col#] )
            IN { "HANDS", "LEGS" }
            && tableA[col2] = "Y",
    "Y",
    "N"
)