data model relationships and calendar dimension

[jscottNRG]

I've been puzzling over this problem for some hours now and hope someone will be able to help.  I'll try to simplify my data model to better illustrate what I'm trying to do.

I have three data tables: products (product IDs, type of product, color, and size), assemblies (product IDs and assembly date), and inspections (product ID and inspection date).  My report looks like this:

 

This is my data model view, with dimension tables for date ("Work Date"), product color, and product size:

 

By using slicers and my dimension tables, I can filter the tables on the report, for example by selecting "small" for product size.  This shows the products with size "small" and the assembly and inspection data for those products as expected:

I want to use the date slicer to choose a date range and show the assembly and inspection data for that range, along with all products that were either assembled or inspected in that range.  What I get is this - note that product_IDs 1, 3, and 4 were inspected on or after 1/6/2018 but it does not show up in the middle product table.  I only can see the products that were both assembled AND inspected in that date range, but that's not what I need.  How do I adjust my data model relationships so that I can see all products that were worked on in the date range I've chosen?  I believe it has something to do with my cross filter directions being Single vs. Both, but I can't seem to get the right configuration.

 
 

Thanks in advance for any help.

J

[v-danhe-msft]

Hi @jscottNRG,

Could you have tried edit below relationship from simple to both?

Regards,

Daniel He

[jscottNRG · Accepted solution]

Thanks all for your help.  Ultimately what I did to fix the issue was to create a bridge table:

1. Create columns with IDs for the "assemblies" and "inspections" tables (e.g. A1 for assembly number 1, I2 for inspection number 2, etc.)

2. Make a new query as a reference of "assemblies", append "inspections"

3. Add relationships between the bridge table and Work Date, inspections, assemblies and products.

This way, when I make a slicer selection in my Work Date (calendar) table, the bridge table shows both assemblies and inspections that occurred in the time range selected.  And most importantly, the middle "products" table is filtered such that a product is shown if it either has an assembly or an inspection in the date range, whereas the previous setup only showed a product if it had an assembly and an inspection in the date range.

[MFelix]

Hi @jscottNRG,

Didn't made a copy of your model but I saw that in the relationship between the tables Assemblies and Products you have bidirectionalaty on try to change it to single and check if it works.

Regards,

MFelix

[jscottNRG]

Thanks for the suggestion @MFelix.  I'm afraid that this won't do what I need, because I need assemblies and inspections to be filtered by changes in the color and size slicers.  At the same time I also need selections made in the date slicer to filter the products table.  So it seems like I'd need both of the relationships - between assemblies and products and between inspections and products - to be bidirectional, but when I tried it Power BI did not allow that.

[MFelix]

Can you share a sample of the report?

If any sensitive data try to make a mockup or send the link through private message.

Regards,
MFelix