0

The issue is we have 3 databases that contain the names of our suppliers we also have data on those 3 databases that we want to merge into 1 pivot table. In order to do this, we need to get a list of unique items in order to do a many-many relationship match as shown in this post:

https://stackoverflow.com/questions/52286712/how-to-create-a-many-many-relationship-in-excels-data-model

  • 1
    we need to get a list of unique items Copy all 3 data arrays onto a worksheet and use "Remove duplicates". – Akina Dec 4 '18 at 9:07
  • Thank you didn't know you could do that. But my concern is if there will be new suppliers that enter the picture. We use auto-refresh on our data models and if there is a missing supplier then it'll be missing in the pivot table I believe – Pherdindy Dec 4 '18 at 9:24
  • Can this be done using DAX? I'm very unfamiliar with it – Pherdindy Dec 4 '18 at 9:30
  • You need to combine all 3 lists into one list (UNION in SQL terms). Yes, DAX can it. See, for example, this article. – Akina Dec 4 '18 at 9:44
  • I have no idea how to place it in the Rows portion of the pivot table though. I used Customer:=DISTINCT(UNION(VALUES('TestCur - Invoice'[CustomerID],'TestDR - Invoice'[CustomerID]))) but returns an error – Pherdindy Dec 4 '18 at 10:17
0

I would prefer to work in the Query Editor using the Append feature to combine the data from the 3 databases into one table. All your data modeling and visualisation tasks will be simpler from then on, for example you wont need to build this table.

However if you just want a quick fix using DAX, it would be along these lines:

All Customers =
UNION (
    UNION (
        DISTINCT ( 'Invoice1'[CustomerID] ),
        DISTINCT ( 'Invoice2'[CustomerID] )
    ),
    DISTINCT ( 'Invoice3'[CustomerID] )
)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.