Weird filtering issue involving many-to-one join - first time ever

[jashjacob]

I have this weird issue that propped up recently. So, I am bringing in data from two tables which are joined via one-to-many join.

Lets call these tables Product and ProductTransactions. They are joined based on the ProductCode. 

Product Table:

ID	Name	Description	Category	Inventory	Cost
ab133	abra	magical moments	toddler	100	5
ab123	ca	in history	adolescent	200	7
ab333	dabra	of entertainment	teenage	500	9

Product Transaction Table

ID	Date	Customer	ProductNo	Quantity	Amount
tr001	6/27/2022	XYX	ab133	10	50
tr123	6/27/2022	ABC	ab133	12	84
tr153	6/27/2022	ABC	ab123	12	108

Ideally, we would expect that if the page is filtered for a few products (not slicers, but page level filters), then the data will be filtered, considering that we are bringing in data from only these 2 tables and the Product Category is coming from the ONE side of the join. However, this is not happening for me.

When I use a slicer and select any of the products already filtered within the page, I get the filtered value. But, if I select all, it goes back to all Categories ignoring the page level filter. What could be happening? Initially I assumed it could be a "meh, needs a restart" issue, but that didn't solve it.

[v-kkf-msft]

Hi @jashjacob ,

I can't reproduce your issue. When I filter using the 'Product Table'[ID] column in both the page filter and the slicer, the correct result is returned.

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

[jashjacob]

Hi,

While trying to prepare a copy, I kinda figured out a possible cause. I was removing some of the tables that were irrelevant to this particular table, and viola, it resolved it. Now the issue I am dealing with is how to fix the issue, without having to remove the other table.

[jashjacob]

Hi Amit,

Thanks for the response. I have removed and added all filters, slicers, and even rebuilt the matrix. The only thing I haven't tried is all of them together.

Is there any reason that you can think of where a filter placed on the one side of a many-to-one join will not affect the table when only 2 tables are in play in this particular page. Even weird is the fact that when I use a sliver and select individual values, it works. To expand on this point, out of 10 categories, this page is filtered for 3 of them. These 3 are available as a slicer. If I select all 3 of them using control, I get just the 3, but if I use "select all" or leave it unselected, I get all categories.

If I don't bring in the transaction table, the filter is still effective. 

[amitchandak]

@jashjacob , This is really strange. Just delete the page level filter and add it again and check if that is working now.

If this does not help
Can you share a sample pbix after removing sensitive data.

[jashjacob]

Let me figure out a way to duplicate this without critical information. I probably need to share this.