locked
Adding Non-Direct Relationships in Power Pivot RRS feed

  • Question

  • Background and Context

    (Please see pictures below for more context)

    Each month I have to create a VAT report for our business which lists every transaction made in the previous month. On the face of the report the vendor name, gross, nett and VAT amounts must be present. All these bits of information are from different sources, but can be linked to a transaction "voucher" which essentially acts as a unique ID for each transaction.

    I use four tables to compile the report which are:

    1. tblUniqueIDs (contains all unique IDs of the transactions, IDs do not repeat)
    2. tblVAT (which contains unique IDs and the VAT amount, IDs repeat)
    3. tblVendors (which contains a list of the vendor names and vendor account numbers, vendor account numbers do not repeat)
    4. tblGross (which contains unique IDs and the gross amount as well as vendor account numbers, IDs and vendor account numbers repeat)

    Question

    How do I associate the vendor name with the unique ID of each transaction? Currently each unique ID can pick up the gross amount via a one to many relationship between tblUniqueIDs and tblGross. tblGross and tblVendors also share a relationship via a one to many relationship between the vendor account numbers and vendor names. Effectively, I want to add the vendor name to each unique ID.

    The pictures below show that even though there is only one vendor account number (and hence one vendor name) per unique ID, excel does not know to which vendor the VAT amount relates to.

    For example, looking at the second transaction (ending in 0002), the VAT has been correctly identified as 478 however it is being shown against every supplier.

    Any help would be greatly appreciated and if you need further information to assist let me know!

    Screenshots

    Can be located at imageshack (dot com) with the following suffix "/a/JAMl/1"

    Apologies for sharing this way, unfortunately my account isn't verified yet.

    Tuesday, January 9, 2018 9:53 AM

Answers

  • Effectively the way you have your data model setup there is a many to many relationship between VAT and Vendors

    If you update your VAT calculation to use the following it should work the way you want as this will pull across the filter context from the tblGross table and apply it to the tblVAT table

    =CALCULATE(SUM([VAT Amount]),tblGross)

    It's a tricky pattern to understand. For more information on many to many patterns in DAX the following whitepaper is definitely worth reading https://www.sqlbi.com/articles/many2many/


    http://darren.gosbell.com - please mark correct answers

    Tuesday, January 9, 2018 10:12 PM

All replies

  • Effectively the way you have your data model setup there is a many to many relationship between VAT and Vendors

    If you update your VAT calculation to use the following it should work the way you want as this will pull across the filter context from the tblGross table and apply it to the tblVAT table

    =CALCULATE(SUM([VAT Amount]),tblGross)

    It's a tricky pattern to understand. For more information on many to many patterns in DAX the following whitepaper is definitely worth reading https://www.sqlbi.com/articles/many2many/


    http://darren.gosbell.com - please mark correct answers

    Tuesday, January 9, 2018 10:12 PM
  • Hi Nicolas__,

    Thanks for your question.

    >>>How do I associate the vendor name with the unique ID of each transaction?
    Based on the data model you have setup, this is a tranditional many to many relationship. Except the whitepaper provided by Darren, you can also refer to below blog:
    https://powerpivotpro.com/2012/11/a-mystifying-and-awesome-solution-for-many-2-many/


    Best Regards
    Willson Yuan
    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Wednesday, January 10, 2018 2:47 AM