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

Need Help

ryan_mayu

4 years ago

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

pls see the attachment below

consolidate.pbix22 KB

FOliveira

Frequent Visitor

4 years ago

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.