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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
kristinak
Frequent Visitor

Calculated column from two different tables

Hello,

 

I am new to Power BI and DAX and I am facing some difficilties. I want to add a calculated column where I need to devide values from two different tables. Relationship between tables is Many to one (I suppose).

The syntax I write is the following:

 

ColumnName = DIVIDE('Table1[Stock], RELATEDTABLE('Table2)[RQTY])

 

Thanks in advance.

1 ACCEPTED SOLUTION
v-caliao-msft
Employee
Employee

@kristinak,

 

Firstly, you need to get data from antoher table, and then devide values. To get data from another table, we cna use RELATED function and Lookupvalue function.
RELATED function returns a related value from another table.
https://msdn.microsoft.com/en-us/library/ee634202.aspx

Lookupvalue function returns the value in result_columnName for the row that meets all criteria specified by search_columnName and search_value.
https://msdn.microsoft.com/en-us/library/gg492170.aspx

 

If this is not what you want, please provide us some sample data and your expected result, so that we can make further analysis.

 

Regards,

Charlie Liao

 

View solution in original post

4 REPLIES 4
v-caliao-msft
Employee
Employee

@kristinak,

 

Firstly, you need to get data from antoher table, and then devide values. To get data from another table, we cna use RELATED function and Lookupvalue function.
RELATED function returns a related value from another table.
https://msdn.microsoft.com/en-us/library/ee634202.aspx

Lookupvalue function returns the value in result_columnName for the row that meets all criteria specified by search_columnName and search_value.
https://msdn.microsoft.com/en-us/library/gg492170.aspx

 

If this is not what you want, please provide us some sample data and your expected result, so that we can make further analysis.

 

Regards,

Charlie Liao

 

Hi again. I figured out I need to use the relatedtable function. 
Let me bring some clarity regarding my intentions. I have two tables, 1 with the monthly inventory report and one (dashboard) with PO data. The relationship that I have created between those two tables is One to manywith crossfilter direction: both. 

Now, what I am trying to calculate in a new calculated column is the sum of inventory (from the monthly report) / sum of PO Qty

 

 

My understanding is, when I need to calculate the Months of stock, I need to divide the current level of inventory qty / qty consumed in the last month. 

 

MOS = DIVIDE(RELATED('Inventory Level'[Stock]), 'Dashboard'[GRQTY]), 0)

 

I am pretty sure I do something wrong either with the relationship between the two tables, or with the DAX syntaxis/functions.  

Thank you for your help.

Hello,

 

I am new to Power BI and DAX and I am facing some difficilties. I want to add a calculated column where I need to devide values from two different tables. Relationship between tables is Many to one (I suppose).

The syntax I write is the following:

 

ColumnName = DIVIDE('Table1[Stock], RELATEDTABLE('Table2)[RQTY])

 

Thanks in advance.

@kristinak

1. what issue are you facing exactly i.e what errors are you getting?

2. can you provide a screenshot of your relationship view?

3.  do you need a calculated column or measure?

 

 

 

 

 

 

 





If I took the time to answer your question and I came up with a solution, please mark my post as a solution and /or give kudos freely for the effort 🙂 Thank you!

Proud to be a Super User!




Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.