SalesInvoiceLine - ItemAttribute

[data_mp_97]

Hello,
I have a question about how to relate several BC tables in Power BI.
I need to be able to relate the SalesInvoiceLine table with ItemAttribute, in order to get information from both in one table.
Right now, to relate them, I have done the following:

· SalesInvoiceLine (No) - Item (No). Many-to-one relationship
· Item (No) - ItemAttributeValueMapping (No). One-to-many relationship
· ItemAttributeValueMapping (ItemAttributeValueID) - ItemAttributeValue (ID). Many-to-one relationship
· ItemAttributeValue (AttributeID) - ItemAttribute (ID). Many-to-one relationship

With these relationships, if I try to get a value, for example, the quantity from SalesInvoiceLine and another value from ItemAttribute or ItemAttributeValue, Power BI does not establish the relationship between them.

How should I relate SalesInvoiceLine with ItemAttributeValue and ItemAttribute? Any ideas?

Thank you very much in advance, I’ve been trying for several days and haven’t been able to figure it out!

[v-nmadadi-msft]

Hi @data_mp_97 

May I check if this issue has been resolved? If not, Please feel free to contact us if you have any further questions.

Thank you

[data_mp_97]

Hi, no, I haven't been able to solve the problem. I have the bidirectional address they mentioned to me, and it gives me results, but I think they are ambiguous. The problem is as follows:

In itemattributevaluemapping (value, name), I can't combine queries with item27 because only the items match. The same happens between itemattributevaluemapping and salesinvoiceline and salescrmemoline, only a part matches (customers, etc).

Bridge table? How would you do it?

[v-nmadadi-msft · Accepted solution]

Hi @data_mp_97 ,

A bridge table sits between two tables and contains only the keys needed to link them. It breaks a many-to-many relationship into two one-to-many relationships, allowing Power BI to filter data correctly without duplicating or corrupting results.

To know more on how to create a bridge table, Please refer this article:
Connecting Fact Tables in Microsoft Fabric: A Brid... - Microsoft Fabric Community

I hope this information helps. Please do let us know if you have any further queries.
Thank you

[v-nmadadi-msft]

Hi @data_mp_97 

I wanted to check if you had the opportunity to review the information provided by @MattiaFratello , @FBergamaschi . Please feel free to contact us if you have any further questions.

Thank you.

[Syndicate_Admin]

These single directional filters will prevent filtering betwen salesinvoiceline and itemattribute tables. However, changing the direction of the right arrow will effectively result in a many-to-many reltionship between salesinvoiceline and itemattributevaluemap through item.

You're then dealing with a many to many relationship so will want to look at this:

Many to many relationships

[MattiaFratello]

Hi @data_mp_97,

the path of your relationships look correct to me.

Could you please share an example of what exactly it's not working?

[FBergamaschi]

Please include, in a usable format, not an image, a small set of rows for each of the tables involved in your request and show the data model in a picture, so that we can import the tables in Power BI and reproduce the data model. The subset of rows you provide, even is just a subset of the original tables, must cover your issue or question completely. Alternatively, you can share your .pbix via some cloud service and paste the link here. Do not include sensitive information and do not include anything that is unrelated to the issue or question. Please show the expected outcome based on the sample data you provided and make sure, in case you show a Power BI visual, to clarify the columns used in the grouping sections of the visual.

Need help uploading data? click here

Want faster answers? click here

[data_mp_97]

Hello! Thank you very much for the answers.

Now it seems to "work" for me by doing the following:

I have combined queries, bringing the value and the Name (these are the two columns I need) from itemattributevalue to itemattribute, from itemattribute to itemattributevaluemaping, and from itemattributevaluemaping to item.

Now if I put the value or the name from item in a table and put the Quantity from SalesInvoiceLine, it doesn't give me an error, but I don't know if it's the right approach to make so many query combinations.

The main columns of each of the tables are:

item:

-No

-Globaldimension1code

-itemcategorycode

-itemcategoryID

ItemAttributeValeuMapping:

-ItemAttributeID

-No

itemAttribute:

-ID

ItemAttributeValue:

-AttributeID

-ID

SalesInvoiceLine:

-DocumentNo

-DimensionSetID

-No

-ShortcutDimension1Code