Forum Discussion
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.
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.aspxLookupvalue 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.aspxIf 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
4 Replies
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.aspxLookupvalue 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.aspxIf 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.
