topic Re: Get reference from one table to display data in another table in DAX Commands and Tips

Get reference from one table to display data in another table

dprousteau — Mon, 05 Feb 2024 22:24:05 GMT

Dear all,

First of all, thanks a lot to all the posters for all the answers provided to everyone, which helped me go through a lot of things up to now.

Unfortunately I have a syntax question in Power BI and cannot find the answer.

I have 2 tables:
TableInfosRefs containing data (name, price, minimal stock...) for all the parts, with unique identifier idRef

TableInfosRefs

idRef	Name	Price	MinimalStock
100001	Screw	15	200
100002	Nut	20	15
100003	Bolt	25	0

TableJournal containing a journal of the inputs and outputs quantities, based on the identifier idRef. It also contains price, name... which are copied each time an input or output is added in the database.

TableJournal

idJournal	idRef	Date	JournalQuantity	Name	Price
1	100001	20/01/2024	50	Screw	15
2	100001	22/01/2024	-2	Screw	15
3	100002	24/01/2024	100	Nut	20
4	100002	26/01/2024	-20	Nut	20
5	100003	28/01/2024	6	Bolt	25

I want to show a table of all the parts that need to be ordered, if current stock is below minimal stock.

Display Table to order: What I want

idRef	Name	CurrentStock	MinimalStock	QuantityForOrder
[from TableJournal]	[from TableJournal]	[from TableJournal]	[from TableInfosRefs]	[MinimalStock - CurrentStock]
100001	Screw	48	200	152
100002	Nut	80	15	-65
100003	Bolt	6	0	-6

Description of table above: I made a display table based on TableJournal where are shown the idRef, price, name, and current stock (sum of all the inputs and all the outputs on the idRef - the outputs quantities have a "minus" sign in this column)

I want to add to this display table the 2 columns based on TableInfosRefs :
1. MinimalStock for each idRef, idRef being searched in TableInfosRefs for each line of my display table based on TableJournal
2. QuantityForOrder = MinimalStock minus current stock

Display Table to order: What I currently get

idRef	Name	CurrentStock	MinimalStock	QuantityForOrder
[from TableJournal]	[from TableJournal]	[from TableJournal]	[from TableInfosRefs]
100001	Screw	48	215	167
100002	Nut	80	215	135
100003	Bolt	6	215	209

Problem for 1. is that I cannot find how to display the minimal stock for each idRef: It makes the SUM of all my minimal stocks in the TableInfosRefs

Problem for 2.: I cannot find the correct syntax to take the idRef of each line from TableJournal. Here is my formula:

QuantityForOrder =
LOOKUPVALUE('TableInfosRefs'[MinimalStock],'TableInfosRefs'[idRef],xxx) - SUM('TableJournal'[JournalQuantity])

xxx is how to take the idRef for each line from TableJournal. I've tried 'TableJournal'[idRef] with Value, Selected, ThisItem, Each, I cannot find the correct syntax.

I hope you can help me on this topic, thanks a lot!

Re: Get reference from one table to display data in another table

123abc — Tue, 06 Feb 2024 06:41:26 GMT

To achieve the desired output, you can use DAX (Data Analysis Expressions) measures in Power BI. Let's break down the solution into two parts:

Finding Minimal Stock for each idRef:
Calculating QuantityForOrder

First, for the Minimal Stock issue, you can use a combination of SUMMARIZE and RELATED functions to get the minimal stock for each idRef:

MinimalStock =
VAR SummaryTable =
SUMMARIZE(
'TableJournal',
'TableJournal'[idRef],
"MinimalStock", MAX('TableInfosRefs'[MinimalStock])
)
RETURN
SUMX(
FILTER(
SummaryTable,
'TableJournal'[idRef] = EARLIER('TableJournal'[idRef])
),
[MinimalStock]
)

This measure creates a summary table where it groups TableJournal by idRef and extracts the maximum MinimalStock for each idRef from TableInfosRefs. Then, it iterates over each row in the summary table and filters the relevant idRef to get the correct MinimalStock.

Second, for the QuantityForOrder calculation, you need to subtract the CurrentStock from the MinimalStock:

QuantityForOrder =
VAR CurrentStock = SUM('TableJournal'[JournalQuantity])
VAR MinimalStock = [MinimalStock]
RETURN
MinimalStock - CurrentStock

This measure simply subtracts the CurrentStock from the MinimalStock calculated in the first measure to determine the QuantityForOrder.

Ensure you add these measures to your Power BI model and use them in your visualizations. This should give you the desired output where you can see the parts that need to be ordered based on the current stock and minimal stock thresholds.

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

In case there is still a problem, please feel free and explain your issue in detail, It will be my pleasure to assist you in any way I can.

Re: Get reference from one table to display data in another table

Anonymous — Wed, 07 Feb 2024 08:52:46 GMT

Hi dprousteau ,

Here are the steps you can follow：

1. Create measure.

MinimalStock = SUMX( FILTER(ALL('TableInfosRefs'), 'TableInfosRefs'[idRef]=MAX('TableJournal'[idRef])&&'TableInfosRefs'[Name]=MAX('TableJournal'[Name])), [MinimalStock])

QuantityForOrder = [MinimalStock]- SUMX( 'TableJournal',[JournalQuantity])

2. Result:

Best Regards,

Liu Yang

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

Re: Get reference from one table to display data in another table

dprousteau — Mon, 04 Mar 2024 10:16:16 GMT

Thank you SO MUCH Anonymous , it worked perfectly!

Re: Get reference from one table to display data in another table

dprousteau — Mon, 04 Mar 2024 10:17:47 GMT

Thanks a lot 123abc for your help. Unfortunately it didn't work, the EARLIER function told me that there was no earlier version of the number. No need to look further, the answer of Anonymous works perfectly. Once again, thanks everyone for your quick support!