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'm working on a pricing report and I have two main fact tables. Both are the exact same data, except one is for a base period and one is for a current period. The goal is to be able to filter the base period table (say 2022), find the average selling price in that base period, and compare it to a current period (say 2023) and the average selling price in the current period.
I'm running into an issue with the base period table. When i filter the current period table to 2023-06, it also filters the base period table even though the tables aren't directly linked.
You can see in the screenshot below, my total USD sales are 38.5m for the base period. when i filter the current period slicer anything other than 1-9, the base period numbers change.
Here are my relationships. The connection from the calendar table is ONLY connected to the current period.
I can't break filtering on the visual since the visuals will have both base period average selling price and current period average selling price as well as other fields that utilize these two measures to make other measures.
I'd appreciate any help that anyone can provide. Thank you all kindly!
Solved! Go to Solution.
So your solution didn't work, but by gods you helped me find the issue after staring at my screen for so long. I made your new table but realized it wasn't going to work because I needed filters from other tables to apply (for example, I'd need to exclude certain bad item codes from both the base period and the current period). I recreated all the connections from the various lookups into this new table and realized it was a problem with the LU_Customer table. Upon looking at the connection between LU customer and the current period, the cross filter direction was set to 'both' instead of 'single'. Made this direction 'single' and the issue is resolved.
Thank you!
So your solution didn't work, but by gods you helped me find the issue after staring at my screen for so long. I made your new table but realized it wasn't going to work because I needed filters from other tables to apply (for example, I'd need to exclude certain bad item codes from both the base period and the current period). I recreated all the connections from the various lookups into this new table and realized it was a problem with the LU_Customer table. Upon looking at the connection between LU customer and the current period, the cross filter direction was set to 'both' instead of 'single'. Made this direction 'single' and the issue is resolved.
Thank you!
@NickAdmin hello your best bet is to create a disconnected base table by using the formula New table = 'Base Table'
this will create replica of the table but dont connect it to any realtionship
I'll give that a shot and update with results.
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 |
---|---|
104 | |
101 | |
79 | |
72 | |
64 |
User | Count |
---|---|
142 | |
108 | |
101 | |
81 | |
74 |