Forum Discussion

FOliveira's avatar
FOliveira
Frequent Visitor
4 years ago
Solved

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's avatar
    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

FOliveira's avatar
FOliveira
Frequent Visitor
4 years ago

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