Using IN Operator with List of Values

[deepu299]

Hi, 

I am trying to write a DAX function using IN operator. The problem is, I have the list of values which goes between {} are in another table. So I am trying to bring in those values to use in the IN operator. I see the contenateX function is bringing all the values like a list, but they don't work when used with IN. Can someone please help?

VAR _List = CONCATENATEX(FILTER('Static Table', 'Static Table'[Type]= "DIVISION"),CONCATENATE( CONCATENATE("""",'Static Table'[Value]), """"), ",")    /* this is giving me values like "Div1", "Div2", "Div3" */

RETURN
SWITCH(TRUE(),
'Employee'[Division] IN {_List}, "Y", "N")

[amitchandak]

Please refer

CALCULATE (
    sum(sales[Sales Amount]),
    item[brand] IN { "Dell", "Apple" }
)

https://www.sqlbi.com/articles/the-in-operator-in-dax/

https://community.powerbi.com/t5/Desktop/IN-OPERATOR-in-PowerBi/td-p/174285

[deepu299]

@amitchandak - Thanks for your response. I am aware on how to do with the values hardcoding, in my case the values are in another table which keeps changing, so wanted to read through a query and pass to the IN clause. It helps us to control the values from database instead of editing the model everytime. Hope it clarifies on what I am looking for. 

[amitchandak]

I tried it between two unrelated tables. And able to filter values based on compare date on sales.

All sedlected = CALCULATE(sum(Sales[Sales Amount]),Sales[Sales Date] in ALLSELECTED('Compare Date'[Compare Date]))

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks.

My Recent Blog - https://community.powerbi.com/t5/Community-Blog/Comparing-Data-Across-Date-Ranges/ba-p/823601

[deepu299]

@amitchandak  I have tried that before posting here. The problem is my table has lot of other values, so I need to apply FILTER before selecting all the values. I am not able to use FILTER function along with ALL. I don't have anything to compare in the target table, I just need to get list of values to pass to the IN condition.