Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
63 |
User | Count |
---|---|
135 | |
126 | |
110 | |
70 | |
61 |