Calculated column from two different tables

[kristinak]

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.

[v-caliao-msft · Accepted solution]

@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

[kristinak]

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.

[kristinak]

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.

[vanessafvg]

@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?