web
You’re offline. This is a read only version of the page.
close
Skip to main content

Notifications

Announcements

Introducing our New Video Series: Community Spotlight
Community site session details

Community site session details

Session Id :
Dynamics 365 Community / Forums / Small and medium business | Business Central, N... / Item - Itemattribute
Small and medium business | Business Central, N...
Answered

Item - Itemattribute

(6) ShareShare
ReportReport
Posted on by MP-16121410-0 34
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!
Categories:
Dynamics 365 Business Central
Microsoft Dynamics NAV
I have the same question (0)
  • Suggested answer
    RockwithNav Profile Picture
    RockwithNav 8,802 Super User 2026 Season 1 on at
    I can just anticipate, Power BI gets confused because there are too many complicated table links, so filtering doesn’t work properly. The simple solution could be to merge all item and attribute tables into one easy table with item number, attribute name, and attribute value, and then connect it directly to the sales invoice table. This makes filtering sales by attributes work efficiently.
  • MP-16121410-0 Profile Picture
    MP-16121410-0 34 on at
    Hello! Yes, I had tried that, combining queries by gradually bringing these two columns into my item table, it would be easy to get the data from item and salesinvoiceline. The problem I had was when combining the item query with itemattributevaluemapping, since out of 5600 rows, only 5100 matched.

    I modified the table for a cross relationship as my colleague told me: ...
    Now it seems that it does let me use the data from these tables, but it calculates the sum incorrectly. I suppose it's because, as I was told, I now have an indirect many-to-many relationship, but I haven't quite figured out how to handle this.
     
     
    As seen in the picture, the total is not calculated correctly.
     
  • Suggested answer
    Assisted by AI
    OussamaSabbouh Profile Picture
    OussamaSabbouh 9,667 Super User 2026 Season 1 on at
    Hello,
    Your table chain is logically correct, but Power BI won’t propagate filters because Item Attributes are a many-to-many model and need a proper bridge/star schema. The practical solution is to flatten ItemAttributeValueMapping + ItemAttributeValue + ItemAttribute into a single bridge table (ItemNo, Attribute, Value) in Power Query, relate SalesInvoiceLine → Item (Type = Item only), then Item → AttributeBridge, and allow filtering from attributes back to Item (either set cross-filter direction to Both on the Item ↔ Bridge relationship, or use TREATAS in measures). Without that bridge + filter direction, Power BI blocks the relationship, which is why your measures don’t work.
    Regards,
    Oussama 
  • Suggested answer
    YUN ZHU Profile Picture
    YUN ZHU 97,370 Super User 2026 Season 1 on at
    Hi, hope the following can give you some hints.
    Dynamics 365 Business Central: Can we add Item Attributes Factbox (9110, ListPart) to Sales Order (42, Document) page??? Yes, But……
    Dynamics 365 Business Central: Bulk add/update Item Attributes (Edit the attributes of multiple items at the same time) – Customization
     
    Thanks.
    ZHU
  • MP-16121410-0 Profile Picture
    MP-16121410-0 34 on at
    Hello, I have done the following:

    I have combined queries from itemattributevalue to itemattributevaluemapping to bring the columns value and name.

    Then I tried with a cross join between item and itemattributevaluemapping and saw that it did not work. I tried without a cross join and making the relationship with a crossfilter, but it also did not give a good result.

     
    Right now I only have these relationships:

  • Verified answer
    Jeffrey Bulanadi Profile Picture
    Jeffrey Bulanadi 9,099 Super User 2026 Season 1 on at
    HI,


    Your relationships between SalesInvoiceLine → Item → ItemAttributeValueMapping → ItemAttributeValue → ItemAttribute are correct logically, but Power BI will not automatically propagate filters across multiple many‑to‑many hops unless the model is designed with a proper bridge or flattened structure. In other words, Power BI cannot automatically relate SalesInvoiceLine and ItemAttribute solely by chaining those tables* without adjusting the model.

    To make this work, you have two reliable options:

    Option 1: Flatten the relationships in Power Query
    Instead of relying on automatic relationships, merge the tables so that the SalesInvoiceLine table includes the Item attributes directly in a single table.
    Example merges in Power Query:

    • Merge SalesInvoiceLine with Item on Item No
    • Merge result with ItemAttributeValueMapping on Item No
    • Merge result with ItemAttributeValue on ItemAttributeValueID
    • Merge with ItemAttribute on AttributeID
       

    This produces a table where all necessary fields are in one place.

    Option 2: Use a dimension‑bridge design
    Create a bridge table (distinct Item + ItemAttribute + ItemAttributeValue) that relates to both SalesInvoiceLine and the attribute tables. This avoids filters needing to traverse multiple hops and lets Power BI propagate filters correctly.

    A star schema with fact (SalesInvoiceLine) and dimension (Items + Attributes) is the recommended model design.

    The problem you are encountering is due to how relationships and cardinality work in Power BI. Power BI supports many‑to‑many relationships but requires careful modeling for correct filter propagation. A bridge or flattened table avoids ambiguity and ensures visuals return correct data.


    Helpful References

    Item Attributes tables in BC (shows structure and how item attributes work):
    Many‑to‑many relationships explanation (Power BI) (why bridging or flattened design is needed)
    Power BI relationship modeling guidance (best practices for bridging many‑to‑many)


    If you find this helpful, feel free to mark this as the suggested or verified answer.

    Cheers
    Jeffrey

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

News and Announcements

Introducing our New Video Series: Community Spotlight

Quick Links

Responsible AI policies

As AI tools become more common, we’re introducing a Responsible AI Use…

Congratulations to our 2025 Community Spotlights

Thanks to all of our 2025 Community Spotlight stars!

Congratulations to the December Top 10 Community Leaders!

These are the community rock stars!

Leaderboard > Small and medium business | Business Central, NAV, RMS

#1
OussamaSabbouh Profile Picture

OussamaSabbouh 2,164 Super User 2026 Season 1

#2
Dhiren Nagar Profile Picture

Dhiren Nagar 1,242 Super User 2026 Season 1

#3
YUN ZHU Profile Picture

YUN ZHU 1,197 Super User 2026 Season 1

Last 30 days Overall leaderboard

Featured topics

Product updates

Dynamics 365 release plans