PowerPivot - Join two tables

Anything related to PowerPivot and DAX Formuale
ranco11
Posts: 1
Joined: Thu Nov 21, 2019 12:58 am

PowerPivot - Join two tables

Postby ranco11 » Thu Nov 21, 2019 1:10 am

Hello,

I thought I have a simple powerpivot question, which turns out not to be so simple :)

I have e.g. two data files:

file a has four columns)
I: contract_ID, Sales_Name, and Location

file b has three colums:
II: contract_ID, products, sales amount

File b might have multiple rows with the same contract_ID

If I now join both tables via contract_id and create a pivottable with powerpivot and want to show Sales name and corresponding products in the same pivottable then all diferent products which are in file b are shown next to all different sales_names from file a.

What I would like to see are only products which are aktually linked with each individual sales_name in the raw data base.
[b]It works perfectly fine for sales amount in the value field but not for Sales_name and products next to each other in the row field of the pivottable.[/b]

Is there any solution for my problem?

Thanks a lot in advance,

Martin

Bernard Heymans
Posts: 28
Joined: Wed Feb 28, 2018 12:18 am

Re: PowerPivot - Join two tables

Postby Bernard Heymans » Fri Nov 22, 2019 6:23 pm

Hi,

you have to work using ONE place that will be connected to both tables.

Just create a query that will have UNIQUE contract_ID (base on one of the existing table)
and of course add the resulting table to the datamodel.

link that table to both data table.
Hide contract _ID in both datafile to prevent using them in pivots


Create measure in both data files like Countloc := Distinctcount(a[Location]]) , Sales:= Sum(b[TotalSales])

done


Return to “PowerPivot/DAX”

Who is online

Users browsing this forum: No registered users and 0 guests

cron