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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
alann
Frequent Visitor

Loop through two columns to get max result entries

I'm in need of a solution, which I'm sure is possible, but beyond my current PowerBI skill-set.

 

Thanks in advance for your help.

 

I have a table with 4 columns:

  • [Row ID] - a generic row# of the table
  • [Entry No.] - link to an entry in an inventory table
  • [Entry No. (IN)] -  if the [Entry No.] was an inbound entry (an increase to inventory) then identifies the same as [Entry No.]
  • [Entry No. (OUT)] - if the entry was an outbound entry (an decrese to inventory) then identifies the same as [Entry No.]

The purpose of the table is to trace the moment of inventory transactions through the system - from the initial inbound/increase entry through to whe the inventory left/decreased.

 

Below are some examples of increasing complexity:

Example 1

  • [Entry No.] 1 is an increase in inventory of 50
  • [Entry No.] 2 is a decrease in inventory of 50 linked to [Entry No.] 1

example 1.png

 

 

Required result I need is 

outcome 1.png

 

 

Example 2 - slightly more complex

  • same as example 1, but this time two entries [Entry No.] 2 and 3 have 25 each consuming the original 50

example 2.png

 

 

 

Required result I need is

outcome 2.png

 

 

Example 3 - slightly more complex again

  • same as example 1 and 2 except this time [Entry No.] 2, of which 25 came from [Entry No.] 1 gets consumed again into [Entry No.] 4 and 5 at a quantity of 10 and 15 respectively. 
  • [Entry No.] 3 also goes on to be consumed into [Entry No.] 6

example 3.png

 

 

 

 

Required result I need is

outcome 3.png

 

 

Example 4 - another slightly more complex scenario

  • 25 of [Entry No.] 1 goes into [Entry No.] 2 which in-tun goes into [Entry No.] 3 which in-turn goes into [Entry No.] 4 which finally gets split into [Entry No.] 5 and 6.
  • Note 25 of [Entry No.] 1 is still yet to be used, so this needs to show also.

example 4.png

 

 

 

 

Required result I need is

outcome 4.png

 

 

Hopefully from these examples below you can see the table I'm trying to build could be described as: "for each inbound entry (defined as where [Entry No. (IN)] not blank AND [Entry No. (OUT)] = 0) I need to know where and in what quantity it has ended up." 

Where it ended up will always be the MAX([Entry No. (OUT)]). However it's not a rule that the [Entry No. (IN)] is always less than the [Entry No. (OUT)], sometimes (IN) will be higher than (OUT), however the MAX([Entry No. (OUT)] will always be the "where it went" for the higher (IN). example:

example 5.png

 

 

 

Required result I need is

outcome 5.png

 

 

 

You'll see fomr the examples also that any quantities yet to be fully used/consumed should also be visible. 

 

below is a larger set of example data and expected results.

 

Row IDEntry No.Entry No. (IN)Entry No. (OUT)Quantity
111050
221250
333050
443425
553525
664613
774712
888050
998930
101091015
111191115
121210127
131310138
1414111410
151515050
1616151610
1717151710
1818151810
191916195
202017207

 

final outcome.png

 

 

2 REPLIES 2
Anonymous
Not applicable

I'm not sure (a full analysis would take quite a bit of time) but if I were to tackle this... I would look for a better model for my data. Certainly one that'd be clearer and easier from the point of view of data modeling (think: star schema).

Hi Daxer,

 

Thanks for your response, unfortunately I'm working with a dataset that is fixed by Microsoft so not something that is able to be changed; otherwise i would be doing exactly that and building a better data model from the start. It is actuyally a very effective datamodel, and is effectively a for.. each.. next.. loop which I would be able to do it in SQL even with my basic knowledge there; i'm just not sure how to replicate the same in PowerBI. 

 

At the moment i'm looking to create a series of left join merge queries in PowerBI and then a final IF statemtent to grab wherever the last value in each row was found... but i'm certain there is a slicker way hence the community post.

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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

Top Solution Authors