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

Join us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.

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
Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.

June 2025 Power BI Update Carousel

Power BI Monthly Update - June 2025

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

June 2025 community update carousel

Fabric Community Update - June 2025

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