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

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
Anonymous
Not applicable

Conditional join - Limit rows based on running total

Hi all,

 

When using a conditional join I am contronted with some challenges that I am unsure on how to approach.

Hopefully the expertise within this community can guide me in how to proceed.

 

 

My challenge

  • How can I return only the rows from the source query that is required to cover the demand in the target query? E.g. If I have a demand for 650 pcs in the target query, and the running total in the source query is {190,639,739} I would only like to return the rows for the 190 and 639 and not the 739.

 

The M code for the conditional join is as follows. I have also attached the PBIX file for your reference.

= Table.AddColumn(#"Sorted Rows1", "PO Table", each 
//Only applicable for order types "Sales Orders" and "Delivery Orders"
if( [Order Type] = "Sales Order" or [Order Type] = "Delivery Order" and [Running Totals] < 0) then
Table.SelectRows(
RunningTotalsPO,
//Name of table
(PO) =>
//Conditions
[Material] = PO[Material] and
([Running Totals] * [StockQtyPrefix]) <= PO[Running Totals]
//Issue: How to only include the rows required, not all PO rows? (Required = the PO[Running Totals] required to cover [Running Totals]. Not all [PO]RunningTotals

)
else null
)

 

PBIX Material Availability for SalesOrder - PQ Copy_ver2 

 

Regards

Arve

1 ACCEPTED SOLUTION
Anonymous
Not applicable

Hi @v-jingzhang 

 

I see that my question might have been poorly explained.

However, I managed to solve this earlier today.

 

To be able to limit the number of rows being merged I did a initial join of an index. 

I then used this index as the threshold for how many lines that would be merged in the final conditional join.

 

I am sure that I can optimize the M-code by combining some of the code, but at least it seems to be working as intended.

I have attached the PBIX file for reference.

 

 

The conditional join to get the index

= Table.AddColumn(#"Changed Type", "PO Index Table", each 

//Extracting the PO Index of the RunningTotalsPO table. The column PO Index will decide on how many rows that are returned.
//If condition limits where the statement below is applicable
if( [Order Type] = "Sales Order" and [Running Totals On Hand] < 0) then
Table.SelectRows(
RunningTotalsPO,
//Name of table
(PO) =>
//Conditions
[Material] = PO[Material] and
([Running Totals On Hand] * [StockQtyPrefix]) <= PO[Running Totals]

)
else null
)

 

This index was added to the table

arve_haugland_0-1664963516767.png

 

The index in turn was used as the threshold to limit the rumber of rows returned in the final conditional join.

arve_haugland_1-1664963555518.png

 

The end result will look like this in a simple matrix

arve_haugland_2-1664964169526.png

Material Availability for SalesOrder - PQ Copy_ver3 

 

Regards

Arve

View solution in original post

2 REPLIES 2
v-jingzhang
Community Support
Community Support

Hi @Anonymous 

 

Not sure if I understand it correctly. Do you want to filter rows in PO Table value to include rows whose PO[Running Totals] is greater than or equal to (>=) the value in Running Totals column outside? E.g. {639, 739, 741} are all greater than -460, so they should be kept? I am not sure which column is the demand column to be compared with PO[Running Totals]. Can you help clarify it with some examples?

vjingzhang_0-1664956784934.png

 

Best Regards,
Community Support Team _ Jing

Anonymous
Not applicable

Hi @v-jingzhang 

 

I see that my question might have been poorly explained.

However, I managed to solve this earlier today.

 

To be able to limit the number of rows being merged I did a initial join of an index. 

I then used this index as the threshold for how many lines that would be merged in the final conditional join.

 

I am sure that I can optimize the M-code by combining some of the code, but at least it seems to be working as intended.

I have attached the PBIX file for reference.

 

 

The conditional join to get the index

= Table.AddColumn(#"Changed Type", "PO Index Table", each 

//Extracting the PO Index of the RunningTotalsPO table. The column PO Index will decide on how many rows that are returned.
//If condition limits where the statement below is applicable
if( [Order Type] = "Sales Order" and [Running Totals On Hand] < 0) then
Table.SelectRows(
RunningTotalsPO,
//Name of table
(PO) =>
//Conditions
[Material] = PO[Material] and
([Running Totals On Hand] * [StockQtyPrefix]) <= PO[Running Totals]

)
else null
)

 

This index was added to the table

arve_haugland_0-1664963516767.png

 

The index in turn was used as the threshold to limit the rumber of rows returned in the final conditional join.

arve_haugland_1-1664963555518.png

 

The end result will look like this in a simple matrix

arve_haugland_2-1664964169526.png

Material Availability for SalesOrder - PQ Copy_ver3 

 

Regards

Arve

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

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

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.