Filters aren’t responding as expected

[czrogers13]

I have a dataset that includes two tables tied together in a many-one relationship with single-direction filtering. In order, these tables are GL_Expenses (expenses records) to Budget_Items (budget amounts for specific budget accounts). They're linked based on a field named budgetIDLink. I've confirmed that all possible permutations of budgetIDLink exist in Budget_Items. The budgetIDLink value is a concatenation of Division and budget ID. In the Budget_Items table, each budget ID is associated with 5 possible divisions. In the GL_Expenses table, each expense record has a single division and budget ID associated with it. An example budgetIDLink would be "0.0.1IND". 

I have 3 primary visuals created from this relationship: a sum of expenses, a sum of budget amounts, and a visual table with expense record information.

My issue arises in that I want to use the Division field in the Budget_Items table to filter all three of those visuals. When I create a filter slicer and select one of the Division options, say Industrial, the two sum amounts filter as expected, but the table visual doesn't filter at all.

I can't make it a bidirectional filter because it messes up the budget sum (only counts budgets that have expenses associated with them, which isn't helpful when reviewing budgets). I'd prefer to not have 2 Division filters.

I can't figure out why the table won't filter as desired. I know tables can have some weird reactions, but I'm seemingly at a loss as to how to fix this. Help!

[v-diye-msft]

Hi @czrogers13 

if the above posts help, please kindly mark it as a solution to help others find it more quickly. If not, please kindly elaborate more with dummy pbix and expected data, which will be easier for us to provide viable advice. thanks! 

[MFelix · Accepted solution]

Hi @czrogers13 ,

Be aware that when using a one to many relationship on the visualizations where you add the common column you need to use the column on the one side so that everything works properly, meaning that if you have on that table columns that are on both table you need to use that as your slicer and your base on the information.

As a good practice you should hide the column on the many side of the relationship so that you do not use it on visualizations.

[czrogers13]

hey @MFelix,

It's working now! I took your advice and went back to make sure that each filter was connected in the right direction, and I re-implemented the relationships in question. I don't know exactly what the fix was, but I'm guessing I had one of the fields backwards.

Thanks for the help!