Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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
Solved! Go to 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
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
Hi Owen.
Thanks for your help.
The first version was works very well.
Regards,
Andreia