Must have relation ship on two tables

[Hyperchef1969]

Hi,

I have two tables, workorders and items. There is a many to one relation ship. I want to decrease the datamodel in size. In our query software we can relate the workorders and items table as a must have relation, which means that every item in the datamodel must have workorder. Can I manage this also in Power BI, because this will decrease my datamodel significantly in size. Thanks for the reply in advance.

[v-robertq-msft]

Hi, @Hyperchef1969 

According to your description, what do you mean by “In our query software we can relate the work orders and items table as a must-have relation, which means that every item in the data model must have a work order.”? You can explain this in detail so that we can help you in advance.

In Power BI, there are many ways to reduce the model size and improve query efficiency, my suggestion is use “composite models” to connect to the data source, you can use import mode for table ‘items’ and direct query mode for table ‘work orders’, you can follow this link to build a composite model in Power BI to reduce the model size and improve query efficiency

https://docs.microsoft.com/en-us/power-bi/transform-model/desktop-composite-models

Power BI data modeling best practices

Best Regards,

Community Support Team _Robert Qin

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

[Hyperchef1969]

Hi Robert, table items contains all items in our ERP system. Table items is connected to table workorder by Item_ID. An item can have multiple workorders, but there are also items with no workorders. Because of performance issues (memory allocation error when updating in Power BI online), I want to exclude items with no workorder. So that only items are listed in the items table which have one or more workorders (must have relation ship from item to workorder table).

[v-robertq-msft]

Hi, @Hyperchef1969 

According to your description, you want to make the items table shows the items that have one or more work orders to reduce the size of your dataset. I think you can try to filter the ‘items’ table in the Power query editor. When you connect to the data source, you can go to the Power Query editor, click on the ‘items’ table, filter like this:

Then you can click “Apply and close” to load the data model to the Power BI.

Best Regards,

Community Support Team _Robert Qin

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

[Hyperchef1969]

Hi Robert, thanks for your reaction. However item and workorder table are two different tables connected to each other by Item_ID. When I click on the items table there is no reference in that table to number of workorders (0, 1 or more; as you have shown in your filter)

[v-robertq-msft · Accepted solution]

Hi, @Hyperchef1969 

In this situation, I think filter data in the Power query is not a good choice because it can not reduce a lot of data for your dataset. I still think that using the “composite models” to connect to the data source is the best choice, you can use import mode for table ‘items’ and direct query mode for table ‘work orders’, just follow this link to build a composite model in Power BI to reduce the model size and improve query efficiency

https://docs.microsoft.com/en-us/power-bi/transform-model/desktop-composite-models

Power BI data modeling best practices

Best Regards,

Community Support Team _Robert Qin

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

[amitchandak]

The information you have provided is not making the problem clear to me. Can you please explain with an example.
Are planning to bring on required items. What is the size of the item or rows in the item table
Appreciate your Kudos.