Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
FOliveira
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.

Can you please help me in getting the following data consolidated?

 

Source tables:

TBL PURCHASES 
Part NrDeptQTY
ACL10
BCL5
CES15
BES10
ACL5

 

TBL ASSIGNMENTS
Part NrDept
ACL
BES
DES
ACL
BES

 

Consolidated table:

TBL SUMMARY   
Part NrDeptQTY PURCHASEDQTY ASSIGNED *BALANCE
ACL15213
BCL505
CES15015
BES1028
DES01-1

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

Your help would be much appreciated.

Thank you in advance,

Fernando

1 ACCEPTED SOLUTION
ryan_mayu
Super User
Super User

@FOliveira 

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

1.png

pls see the attachment below





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

8 REPLIES 8
Ashish_Mathur
Super User
Super User

Hi,

You may download my PBI file from here.

Hope this helps.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

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



Hi,

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


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Hi Hashish,

 

Thank you very much for putting me back on track.

I was very close, but I messed up in the relationship.
Your very simple schema made it for me 100%
Thank you once again,

Fernando

You are welcome.  Please mark my previous reply (the reply which answered your question) as Answer.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
ryan_mayu
Super User
Super User

@FOliveira 

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

1.png

pls see the attachment below





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




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.

FOliveira_0-1658139141178.pngFOliveira_1-1658139175622.png

 


Any solution for this, please?
Thank you,

Fernando

@FOliveira 

append 1 is already a dim table.

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





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.