Slicer Not Working Across one USERELATIONSHIP field, is Working Across Another

[sled1419]

I have essentially 3 data sets that matter for this scenario - Open Orders, Shipped Orders, and Forecast.  Each of these tables has a column with the $ value, the sales rep name, and the month name. 

I am building a combo bar & line chart, where the bar chart stacks the open & shipped orders by month, and the forecasted amount for the month is shown as a line. 

I set up relationships across Shipped <--> Forecast and Shipped <--> Open to link sales rep name. These are many to many and are set to active.

I also set up relationships across Forecast <--> Shipped and Forecast <--> Open to link the month name. These are many to many and are set to inactive.

I'm using month as my X axis, and I'm pulling it from my forecast table, but I need the other tables to also split by month so I have them calculated using USERELATIONSHIP since the relationships are inactive. It works fine, see below.

My issue comes when I try to filter by sales rep. I have a slicer on the page that has sales rep name, pulled from the Shipped table. When I click the slicer, the Shipped (light blue bar) and Forecast (orange line) filter properly, but the Open (dark blue bar) does not filter, even though it has an active relationship on this field. See below.

I'm not sure why the slicer is working on the Shipped <--> Forecast active relationship, but not working across the Shipped <--> Open active relationship. My assumption would be since my Open value (dark blue) in the visual is the "USERELATIONSHIP" one, which it has to be to get the months split out. But this doesn't make sense because it's the same for the Forecast value (orange line), so I'm not sure why it would be working to slice one USERELATIONSHIP calculation but not the other.

Help greatly appreciated!

[v-ssriganesh]

Hi @sled1419,
May I ask if you have resolved this issue? If so, please mark it as the solution. This will be helpful for other community members who have similar problems to solve it faster.

Thank you.

[v-ssriganesh]

Hi @sled1419,

Thank you for reaching out to the Microsoft Fabric Forum Community, and thanks to @Deku for sharing valuable insights.

Could you please confirm if your query has been resolved by the provided solution? If so, please mark it as the solution. This will help other community members solve similar problems faster.

Thank you.

[Deku · Accepted solution]

You can turn this into a star schema and make things more simple, predictable and performant.

I would suggest turning the month name in a date, start or end of month. 

Create a date dimension, as a role playing dimension.

Setup relationships from date table to each of your facts and use USERELATIONSHIP to activate then.

Then you don't have to worry about other filters from the tables giving unexpected results.

Additionally I would include a common dimensions for sales rep.