Relationship Issues

[Beefheart]

Hello Everyone,

I think I have a relationship problem.

I’ve attached a pbix and a screenshot of the relationships is below.

Basically, we have customers that own assets and those assets have a schematic. Each schematic has a list of components along with the number of components used in the schematic.

Additionally, there’s the typical ERP relationship with customers having sales orders that have item lines that also link to an item.

If I use a table visual and add the customer’s name, asset serial number, schematic code and item quantity, that works. But as soon as I add the Item Code, Power BI can’t display the visual and says that it can’t determine the relationship.

My first thought is that this is because the relationship between the customer and item can be achieved two ways and Power BI doesn’t know which one to use. So I deleted the relationship between the sales order transaction lines and the items. Even though there’s now a single means of establishing the relationship it still won’t work.

Next, I reproduced this data in a SQL Server database and wrote a simple query that used Inner Joins and it worked without a problem.

Power BI won’t allow me to alter the joins so I’m at a loss on what to do.

Can anyone tell me how I could show all the components that a customer could need given their assets please?

I hope what I’ve described makes sense.

Best regards,

Beefheart.

PBIX File (41kb)

https://ufile.io/9vz504l3 

[LP280388]

Hi @lbendlin ,

Powerbi expects an aggregation when you are bringing a column from tables which are not related directly like in your case.

So, Probably you can use an aggregation on your Item Code column when you add it in the visual.  Item Code, I guess its a String so you can choose an aggregation as first/last as shown below.

[lbendlin]

Great comment.  You would still have to make sure that the chosen aggregation makes business sense.

For example here I have no idea if the displayed data makes sense.

[Beefheart]

Hi lbendlin,

I see, that's a bit unfortunate.

But thank you none the less for taking the time to help, it's very much appricated.

Kind regards,

Beefheart.

[Beefheart]

Hello,

Does anyone have any suggestions on I can show the items that fit on the assets a customer has please?
I'm struggling to understand what the problem is.

Thank you for your time, whoever you may be!

Beefheart.

[Beefheart]

Hello lbendlin,

Thank you very much for your help.

Unfortunatly, my problem persists, I cannot put the Item's Code in the table on Page 1 without it breaking and giving a "Can't determine relationships between fields" error.

Do you have any advice on how I can get this working please?

Thanks again for your help,

Beefheart

[lbendlin]

Did you make changes to the data model? Can you please post the updated pbix file?

[lbendlin]

My first thought is that this is because the relationship between the customer and item can be achieved two ways and Power BI doesn’t know which one to use.

No, Power BI would have complained about the ambiguity and would have made one of the relationships inactive.

Think of it in terms of "Can I get there from here?" In Power BI that means "Follow the arrows".  

Here is your data model, slightly rearranged. Dimensions on top, facts at the bottom

(Bidirectional 1:1 relationships often mean that the tables can be combined.)

As you can see your Item Code cannot travel to the customer side.

This data model might be more appropriate:

[Beefheart]

Hello lbendlin,

Thank you for replying!

Yes, I changed the relationships to match those in your second screenshot.

I then tried adding the Items[Code] column to the table in the report view and it stopped the table visual from working.

[EDIT: Link changed to use dropbox]

Here's a link to the latest version https://www.dropbox.com/s/ouhxi9iufc22sf0/Relationships.pbix?dl=0 

Many thanks for your help and sorry to take up your time.

Beeheart.

[lbendlin]

Looks like you linked to the virtual machine image instead of the pbix file.  Might want to correct that.

[Beefheart]

Hi lbenflin,

I've changed the link to a dropbox account that shares the .pbix file.

Thanks for your help and sorry I'm taking up your time.

Best regards,

Beefheart.

[lbendlin · Accepted solution]

There is no relationship between customers and components. Therefore you cannot show these in the same visual.

Same issue between items and assets.

You could work around this by denormalizing some of the tables,  but it may also be a case where the question you are trying to answer with the report needs to be reconsidered.