Forum Discussion

musafasih's avatar
musafasih
Frequent Visitor
5 years ago

Filtering column in one table using values in a column in another table

Hi,   I have got three tbales in my model : the Revenue Accounts, Sales Order List and Sales Order Lines. SO list is connnected to both Rev ACs and SO lines by a one to many relationship. Rev Acs a...
Need Help
Show and Tell
musafasih's avatar
musafasih
Frequent Visitor
5 years ago

HI edhans 

 

Thanks for reaching out. This is what the tables looks like:

 

 

 

SO list table has a one-to-many relationship with both Rev Accounts table and SO lines table. I want the result such that the SO number in SO lines table is able to filter values by SO number in Revenue Accounts. Notice that there are numbers in SO lines that do not exist in Rev Accounts. 

 

 

v-jingzhang's avatar
v-jingzhang
Community Support
5 years ago

Hi musafasih 

 

You could add an inactive many-to-many relationship between RA table and SO Lines table on SO number column with a Single (SO Lines filters RA) cross filter direction. Then use USERELATIONSHIP() function in DAX codes to activate this inactive relationship. 

 

You can also use TREATAS function to filter a table by a disconnected table in DAX codes.

 

Additionally, SO List table already has relationships with both RA table and SO Lines table, so actually there is a relationship between RA table and SO Lines table through SO List table. You can modify the cross filter direction of both relationships to Both, then SO Lines table can filter RA table through the relationship.

 

Regards,
Community Support Team _ Jing
If this post helps, please Accept it as the solution to help other members find it.