Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
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.
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.
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.
@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.
Let me figure out a way to duplicate this without critical information. I probably need to share this.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
97 | |
85 | |
70 | |
61 |
User | Count |
---|---|
151 | |
121 | |
104 | |
87 | |
67 |