Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
andreiasanz
Frequent Visitor

IN clause SQL to DAX

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

1 ACCEPTED SOLUTION
OwenAuger
Super User
Super User

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

 


Owen Auger
Did I answer your question? Mark my post as a solution!
Blog
Twitter
LinkedIn

View solution in original post

2 REPLIES 2
OwenAuger
Super User
Super User

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

 


Owen Auger
Did I answer your question? Mark my post as a solution!
Blog
Twitter
LinkedIn

Hi Owen.

 

Thanks for your help.

The first version was works very well.

 

 

Regards,

Andreia

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.