Forum Discussion
Get reference from one table to display data in another table
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!
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
4 Replies
- 123abc
Community Champion
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 - CurrentStockThis 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.
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!
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
Thank you SO MUCH Anonymous , it worked perfectly!
