cancel
Showing results for
Did you mean:
Frequent Visitor

## How to consolidate data from 2 tables

Hi everyone,

I am stil giving my first steps with PBI.

I am making a report that monitors the Purchased Licenses vs. Assigned licenses for each department.

Source tables:

 TBL PURCHASES Part Nr Dept QTY A CL 10 B CL 5 C ES 15 B ES 10 A CL 5

 TBL ASSIGNMENTS Part Nr Dept A CL B ES D ES A CL B ES

Consolidated table:

 TBL SUMMARY Part Nr Dept QTY PURCHASED QTY ASSIGNED * BALANCE A CL 15 2 13 B CL 5 0 5 C ES 15 0 15 B ES 10 2 8 D ES 0 1 -1

* Assigned quantity is the Count of "Part Nr" for each "Dept"

Your help would be much appreciated.

Fernando

1 ACCEPTED SOLUTION
Super User

1. use PQ to create a dim table to get all the combination of PART and DEPT

2. create measures

``````QTy purchase = sumx(FILTER('PURCHASES','PURCHASES'[Part Nr]=max('Append1'[Part Nr])&&'PURCHASES'[Dept]=max('Append1'[Dept])),PURCHASES[QTY])+0

qty assigned = COUNTAX(FILTER('ASSIGNMENT','ASSIGNMENT'[Part Nr]=max('Append1'[Part Nr])&&'ASSIGNMENT'[Dept]=max('Append1'[Dept])),'ASSIGNMENT'[Dept])+0

qty assigned = COUNTAX(FILTER('ASSIGNMENT','ASSIGNMENT'[Part Nr]=max('Append1'[Part Nr])&&'ASSIGNMENT'[Dept]=max('Append1'[Dept])),'ASSIGNMENT'[Dept])+0``````

pls see the attachment below

Proud to be a Super User!

8 REPLIES 8
Super User

Hi,

Hope this helps.

Regards,
Ashish Mathur
http://www.ashishmathur.com
Frequent Visitor

Hi again Hashish,

Can you please explain this formula, and what is the coalesce function?

Quantity assigned = coalesce(COUNTROWS(Purchases),0)

The formula
Quantity purchased = coalesce(SUM(Purchases[QTY]),0)
Makes sense

Finally, do you have a solution to remove the lines with 0 Purchases and 0 Assignments?
Thank you

Super User

Hi,

Please red up here. - COALESCE function (DAX) - DAX | Microsoft Docs.  Use filters to remove unwanted rows.

Regards,
Ashish Mathur
http://www.ashishmathur.com
Frequent Visitor

Hi Hashish,

Thank you very much for putting me back on track.

I was very close, but I messed up in the relationship.
Thank you once again,

Fernando

Super User

Regards,
Ashish Mathur
http://www.ashishmathur.com
Super User

1. use PQ to create a dim table to get all the combination of PART and DEPT

2. create measures

``````QTy purchase = sumx(FILTER('PURCHASES','PURCHASES'[Part Nr]=max('Append1'[Part Nr])&&'PURCHASES'[Dept]=max('Append1'[Dept])),PURCHASES[QTY])+0

qty assigned = COUNTAX(FILTER('ASSIGNMENT','ASSIGNMENT'[Part Nr]=max('Append1'[Part Nr])&&'ASSIGNMENT'[Dept]=max('Append1'[Dept])),'ASSIGNMENT'[Dept])+0

qty assigned = COUNTAX(FILTER('ASSIGNMENT','ASSIGNMENT'[Part Nr]=max('Append1'[Part Nr])&&'ASSIGNMENT'[Dept]=max('Append1'[Dept])),'ASSIGNMENT'[Dept])+0``````

pls see the attachment below

Proud to be a Super User!

Frequent Visitor

Hello Ryan,

It turns out that your sollution works better for me.

Thank you very much.

I have one doubt: In my report I have a slicer based on a "Department table"
So, I added the table Department to your model, established a One to Many relation with the Append1 table, but it is not filtering.

Thank you,

Fernando

Super User

append 1 is already a dim table.

you can use the dept column in that table to filter.

Proud to be a Super User!