IN clause SQL to DAX

[andreiasanz]

Good afternoon,

I'm having trouble passing the following sql statement to DAX, my problem is the IN clause.

Anyone can help? 

SELECT *
FROM TABLE1
WHERE COLUMN1 IN (
          select COLUMN1
          from TABLE2
          where COLUMN2 IN(
                    select COLUMN2
                    from TABLE3
                    where COLUMN5 = 'XPTO'))

Regards

[OwenAuger · Accepted solution]

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

[andreiasanz]

Hi Owen.

Thanks for your help.

The first version was works very well.

Regards,

Andreia