Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
97 | |
96 | |
81 | |
74 | |
66 |
User | Count |
---|---|
129 | |
106 | |
106 | |
86 | |
72 |