Filter table based on value from another table

[jdubs]

I have two tables ('Accounts' and 'Orders Created Last week' that have a relationship based on AccountID.

I want to create a new table filtering the Accounts table by those with Orders Created Last week. 

I have tried this,

'Accounts w/ Orders Last Week' = FILTER('Accounts', Accounts(AccountID) = RELATEDTABLE('Orders'[AccountID]))

...but I get the error that a "single value for AccountID cannot be determined" (which is technically correct as multiple Orders exist for each Account. So how do I filter this table?

[mahoneypat · Accepted solution]

This would be better done in the query editor or just do this filtering in your measure(s), but if not, please try this DAX table expression

'Accounts w/ Orders Last Week = FILTER('Accounts', Accounts[AccountID] in VALUES('Orders'[AccountID]))

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat

[jdubs]

Thanks. I was using RELATEDTABLE instead of VALUES. That was my problem.

[Greg_Deckler]

Perhaps:

'Accounts w/ Orders Last Week' = FILTER('Accounts', Accounts(AccountID) IN RELATEDTABLE('Orders'[AccountID]))

Otherwise, sample data and such would be very helpful.