Forum Discussion
Using IN Operator with List of Values
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")
4 Replies
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
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.
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
