How to write a DAX measure equivalent to SQL select.. in?

I've worked with SQL for over twenty years, so it comes quite naturally to me. Sometimes it's easier to think in terms of SQL rather than DAX. So I'm going to show you two ways (there are others) to write a "select .. in" using DAX.

For both examples, image you want to do this:

select sum(column1) from tableSource where column2 in (select column3 from tableFilter)

So, the first way is using CONTAINS (https://learn.microsoft.com/dax/contains-function-dax):

CALCULATE ( SUM ( tableSource[column1] ), FILTER ( tableSource, CONTAINS ( tableFilter, tableSource[column2 ], //these are the columns, in each table, you are comparing tableFilter[column3 ] ) ) ) You can do it with multiple columns. For instance, suppose you want this:

select sum(column1) from tableSource

where (column2, column4) in (select column3, column5 from tableFilter)

CALCULATE ( SUM ( tableSource[column1] ), FILTER ( tableSource, CONTAINS ( tableFilter, tableSource[column2 ], tableFilter[column3 ],

tableSource[column4],

tableFilter[column5] ) ) )

Another way is using DAX IN operator (https://learn.microsoft.com/dax/dax-operator-reference):

CALCULATE ( SUM ( tableSource[column1] ), FILTER ( ALL(tableSource),

//don't forget to use the parenthesis here ( tableSource[column2 ], tableSource[column4])

//bellow you need to select only the columns used to filter IN SELECTCOLUMNS(tableFilter, "column3", tableFilter[column3], "column5", tableFilter[column5]) ) )

Pretty good, right? Hope it helps.

#postgres #powerbi #sql #dax #microsoft