Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Don't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.

Reply
dprousteau
New Member

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   
    
idRefNamePriceMinimalStock
100001Screw15200
100002Nut2015
100003Bolt250


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     
      
idJournalidRefDateJournalQuantityNamePrice
110000120/01/202450Screw15
210000122/01/2024-2Screw15
310000224/01/2024100Nut20
410000226/01/2024-20Nut20
510000328/01/20246Bolt25

 

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   
     
idRefNameCurrentStockMinimalStockQuantityForOrder
[from TableJournal][from TableJournal][from TableJournal][from TableInfosRefs][MinimalStock - CurrentStock]
100001Screw48200152
100002Nut8015-65
100003Bolt60-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  
     
idRefNameCurrentStockMinimalStockQuantityForOrder
[from TableJournal][from TableJournal][from TableJournal][from TableInfosRefs] 
100001Screw48215167
100002Nut80215135
100003Bolt6215209

 

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!

1 ACCEPTED SOLUTION
v-yangliu-msft
Community Support
Community 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:

vyangliumsft_0-1707295919033.png

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

View solution in original post

4 REPLIES 4
v-yangliu-msft
Community Support
Community 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:

vyangliumsft_0-1707295919033.png

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 @v-yangliu-msft , it worked perfectly! 

123abc
Community Champion
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:

  1. Finding Minimal Stock for each idRef:
  2. 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 @v-yangliu-msft works perfectly. Once again, thanks everyone for your quick support!

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.

Jan25PBI_Carousel

Power BI Monthly Update - January 2025

Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.

Jan NL Carousel

Fabric Community Update - January 2025

Find out what's new and trending in the Fabric community.