IN clause SQL to DAX

Hi andreiasanz

You can use TREATAS or IN to perform the function of the SQL IN operator.

I would start from the innermost SELECT and turn each SELECT into a CALCULATETABLE. For columns that are used as filters, VALUES(...) is sufficient to get the distinct values of the column.

Here are two versions:

Result =
VAR Table3_Column2 =
    CALCULATETABLE ( VALUES ( TABLE3[COLUMN2] ),
    TABLE3[COLUMN5] = "XPTO"
)
VAR Table2_Column1 =
    CALCULATETABLE (
        VALUES ( TABLE2[COLUMN1] ),
        TREATAS ( Table3_Column2, TABLE2[COLUMN2] )
    )
RETURN
    CALCULATETABLE (
        TABLE1,
        TREATAS ( Table2_Column1, TABLE1[COLUMN1] )
    )

Result =
VAR Table3_Column2 =
    CALCULATETABLE ( VALUES ( TABLE3[COLUMN2] ),
    TABLE3[COLUMN5] = "XPTO"
)
VAR Table2_Column1 =
    CALCULATETABLE (
        VALUES ( TABLE2[COLUMN1] ),
        TABLE2[COLUMN2] IN Table3_Column2
)
RETURN
    CALCULATETABLE (
        TABLE1,
        TABLE1[COLUMN1] IN Table2_Column1
)

Regards,

Owen