Inventory Internal Transfers Issue with in/out WH in one operations table

[George1973]

Hi All,
I have a operations data table with the following layout:

Operation_ID	Oper_Date	Oper_Type_ID	Oper_Type_Name	Client_Venfor_ID	Stock_to	Stock_From
123	05-01-2021	47	Sales		0001
124	12-01-2021	47	Sales		0001
125	14-01-2021	47	Sales		0002
126	15-01-2021	13	Transfer		0002	0001
127	20-01-2021	13	Transfer		0003	0002
128	21-01-2021	47	Sales		0003
129	15-01-2021	47	Sales		0003
130	20-01-2021	47	Sales		0001
131	21-01-2021	47	Sales		0001
132	15-01-2021	40	Import		0001
133	20-01-2021	40	Import		0001
134	21-01-2021	13	Transfer		0002	0001
135	15-01-2021	40	Import		0001
136	20-01-2021	13	Transfer		0001	0003
137	21-01-2021	13	Transfer		0001	0003

The "Operations Details" data table relates to that table by "Operations_ID". but it's not the issue here.

When it comes to calculate stock level per Stock(WH), mainly I have to refer "Stock_to" column, but when there is "Transfer" (Internal Goods Movement) I have to consider the "Stock_From" column too.. And that's my problem. I do have only one relationship with the Stock(WH) data table and can not do two relationship in the same time. Please help to solve this issue.

[Daryl-Lynch-Bzy · Accepted solution]

Hi @George1973 - you can have two relationship between the fact table and the Stock(HW) table.  The second relationship will be inactive.  You formula will need to activate the relationship when the "Transfer" is used.   Could you please upload a sample pbix file to help config the data model and measures?

[v-robertq-msft · Accepted solution]

Hi, 

According to your requirement, you want to use another column as the related column when the Operation Type is “Transfer”. I suggest you to use the calculated column like this to give them the relationship based on the [Stock_From] manually:

stock level per Stock=

IF([Stock_From]=”Transfer”,

Calculate(MAX(Operations_ID [stock level])),Filter(ALL(Operations_ID),[Stock_to]=Earlier(Operations Details[Stock_to])&& [Stock_From]=Earlier(Operations Details[Stock_ From])),

Operations_ID [stock level])

You can transform the DAX formula based on your table names and column names within the dataset and check if it can work.

Thanks in advance!

How to Get Your Question Answered Quickly 

Best Regards,

Community Support Team _Robert Qin

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[v-robertq-msft · Accepted solution]

Hi, 

According to your requirement, you want to use another column as the related column when the Operation Type is “Transfer”. I suggest you to use the calculated column like this to give them the relationship based on the [Stock_From] manually:

stock level per Stock=

IF([Stock_From]=”Transfer”,

Calculate(MAX(Operations_ID [stock level])),Filter(ALL(Operations_ID),[Stock_to]=Earlier(Operations Details[Stock_to])&& [Stock_From]=Earlier(Operations Details[Stock_ From])),

Operations_ID [stock level])

You can transform the DAX formula based on your table names and column names within the dataset and check if it can work.

Thanks in advance!

How to Get Your Question Answered Quickly 

Best Regards,

Community Support Team _Robert Qin

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Daryl-Lynch-Bzy · Accepted solution]

Hi @George1973 - you can have two relationship between the fact table and the Stock(HW) table.  The second relationship will be inactive.  You formula will need to activate the relationship when the "Transfer" is used.   Could you please upload a sample pbix file to help config the data model and measures?

[George1973 · Accepted solution]

Hi,

I did as you suggessted and used formula "Userelashioship" and it works!

It's a inactive relationship and here is the formula:

Total Transfer Out Related = 
CALCULATE(Measures_Inventory[Total Transfer Out],
USERELATIONSHIP(C_SKLAD[Stock_IN ID],TS_OPERATIONS[Stock_out ID]))