Forum Discussion
Helper IIUse a List as a table filter in DAX
Is it possible to essentially use a table/ list to filter a another table in dax?
Right now I have a table that has material and a pegged material. Each material in the table can have multiple pegged materials, so first I store a calculated table that's got the pegged materials related to the material in the given row
Var list = summarize(filter(table, material = earlier(material), pegged material)
Then I want to return a table where the material is only the pegged material list above... Something like...
Var peggedtable = summarize (filter(table, only material in list), material)
Is this possible? I can't seem to get it to work. Tried naturalinnerjoin, crossjoin with no luck.
Well I solved it. For anyone curious. Natural InnerJoin was the solution after all, just needed to fix the data lineage. Worked great after that.
4 Replies
User5231To be Clearer, Right now I am explicity calling out up to 10 variables to filter the Table. This seems clunky and it could miss a value if the pegged materials exceed 10 per material.
- User5231
Well I solved it. For anyone curious. Natural InnerJoin was the solution after all, just needed to fix the data lineage. Worked great after that.
- User5231
Also, to simplify this further, I moved the naturalinnerjoin to the filter in table 1.
This is a one man thread but I could not find a solution to pass a list as a filter in dax anywhere. Everything directed you to power query or discretely listing out the filters. Hopefully someone can find some use here besides me.
- User5231
I discovered an even easier solution is to use the in Operator. If you have a list defined as a signle column variable, you can use the in operator to filter the table using the desired column list you have, whether is it explicit or gererated from a dax expression. Super handy.