Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
czrogers13
New Member

Filters aren’t responding as expected

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!

1 ACCEPTED SOLUTION
MFelix
Super User
Super User

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.


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



View solution in original post

3 REPLIES 3
v-diye-msft
Community Support
Community Support

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! 

 

Community Support Team _ Dina Ye
If this post helps, then please consider Accept it as the solution to help the other members find it more
quickly.
MFelix
Super User
Super User

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.


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



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!

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.