Lookup Table not Filtering Data Table Correctly

[Anonymous]

Hello,

I am working on a budget report and I am running into a few issues that I think stems from one issue.

Scenario 

I have an expenses table with several columns (e.g., department, department subcategory, expense category). I would like to filter this table by the department and department subcategory (and maybe others if possible). I am finding that if I filter the expenses table by the department, additional rows are added to the list. The catch here is that I noticed that if there is a commonality within department subcategory under other departments, those departments are included in the list.

If I filter the expense table with the department subcategory, everything seems to work fine. This leads me to believe that the issue is with the department column.

I have the master lookup table connected to the expense table to handle filtering.

Thanks for help in advance.

[v-xicai]

Hi  @Anonymous  ,

Does that make sense? If so, kindly mark the proper reply as a solution to help others having the similar issue and close the case. If not, let me know and I'll try to help you further.

Best regards

Amy

[amitchandak]

@Anonymous , not very clear. Many to many join is not fine.

If needed create a combined key and join on that (In both table )

Key = [Department] & " " &  [Department Sub Category] // add more columns as per need

[Anonymous]

@amitchandak could you elaborate? I would like to offer the ability to filter using just the department, and/or the department subcategory while both filtering each other.

[v-xicai]

Hi @Anonymous ,

You may change the Cross filter direction of relationships among the two tables above from Single to Both , which will take these tables treated as a single table. Then when you make some changes or interact in someone table, the other table  objects will return corresponding matched result.  See more:Create and manage relationships in Power BI Desktop .

Best Regards,

Amy 

Community Support Team _ Amy

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.