Summarize Column.... am i being to "Excel"... arghhhh

[paulj1]

How do I add a pbix file to this post ?

So I have two data sources, one "Eaten", the other "Purchased".

PurchasedEaten 

Problem 1.... the two tables have common items, and both have unique items, so i want a consolidated table removong duplicates.. so i created a new table using...

SummarizeAllItems = union(SUMMARIZECOLUMNS(Eaten[Item]),
SUMMARIZECOLUMNS(Purchased[Item]))

This creates the table below, trouble is with duplicates......   So i created a second table...

Using this

NoDuplicates = SUMMARIZECOLUMNS(SummarizeAllItems[Item])

Added columns of;
Eaten = calculate(sum(Eaten[Eaten]),filter(Eaten,Eaten[Item]=NoDuplicates[Item]))
Purchased = calculate(sum(Purchased[Purchased]),filter(Purchased,Purchased[Item]=NoDuplicates[Item]))

SummarisedNo Duplicates with SUM

Doesnt seem very elegent, think i'm being to "Excel".....  how do i do this more elegantly....

Problem2........

When i then do a visual, the slicer doesnt work on location....... suspect its because ive done the first bit in a clunky way.... Help....

Output Table Slicer doesnt work

[samdthompson · Accepted solution]

Hi, in this situation i would use the powerquery end to tidy up the data. Three solutions here either a DAX table like you were trying or Merge or Append in power query:

DAX:

Joiny Table = UNION(SUMMARIZE(Purchased,Purchased[Item],Purchased[Location]),SUMMARIZE(Eaten,Eaten[Item],Eaten[Location]))

There are other solutions in DAX too with CROSSJOIN but in my opinion the above is the most straight foward.

Merge:

Append:

// If this is a solution please mark as such