Forum Discussion
Get reference from one table to display data in another table
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
Community ChampionTo 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.
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!
