cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
User5231
Helper I
Helper I

Use 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.

1 ACCEPTED SOLUTION
User5231
Helper I
Helper I

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.

Solution.png

View solution in original post

3 REPLIES 3
User5231
Helper I
Helper I

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
Helper I
Helper I

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.

Solution.png

View solution in original post

User5231
Helper I
Helper I

To 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.

 

2021-10-23 18_07_55-Window.png

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.