Simple JOIN

[bcap01]

Hello,

I am a PowerBI/DAX newbie. I am looking to understand how to JOIN two tables to do a simple calculation. 

For example, lets say I have two tables Invoices and Costs that JOIN based on JobID.  

Invoice Table -  SELECT JobID, InvoiceAmt FROM Invoices

Cost Table - SELECT JobID, Cost FROM Costs

In DAX Format I would like to join these 2 tables to show the calculation:  GP = [InvoiceAmt] - [Cost]

WHat I have tried is the following but got error message.

GP = (Invoice[InvoiceAmt]) - (Costs[Cost])

Any help is sincerely appreciated!

[Anonymous · Accepted solution]

HI @bcap01,

You cannot directly use different table columns in one formula, you can try to reference or use current table value to lookup corresponds table values and use this in calculations.

Using RELATED and RELATEDTABLE in DAX - SQLBI

Power BI Compares Two Columns in Different Tables - SPGuides

Regards,

Xiaoxin Sheng

[bcap01]

the error message i am getting is, "A single value for column '' in table '' caanot be determined. This can happen when a measure formula refers to a column that contains values without specifiying an aggregation..."