How to apply this dynamic search concept using Power BI desktop?

[Newbie_SQLPower]

I want to apply this search concept using Power BI desktop:

Search : Inventory ID (Column C) + LOT Serial Nbr (Column D)

1st Search : should be an Issue Transaction Type (Column A) + Inventory ID (Column C) + Lot Nbr (Column D) : Each result should be searched in 2nd Search. We need to use the result in (Column G and H) for the next search. 2nd Search : for each result in the 1st Search, we should look for Receipt Transaction type (Column A) with the production Nbr found in the 1st Search (Column G) and the inventory ID of (Column H) into the (Column C) Loop of 1st Search : for each result in the 2nd Search, we should be an Issue Transaction Type (Column A) + Inventory ID (Column C) + Lot Nbr (Column D) : Each result should be searched in 2nd Search. We need to use the result in (Column G and H) for the next search. Loop of 2nd Search : for each result in the loop 1st Search, we should look for Receipt Transaction type (Column A) with the production Nbr found in the 1st Search (Column G) and the inventory ID of (Column H) into the (Column C)

Here the link of the data source (excel sheet):

 https://docs.google.com/spreadsheets/d/1bbFBW5DCCVM6ej_b_A6BqrXf4AATsBDU/edit#gid=1594329084

Here the output as an image: 

 Could you please help me to do this! Thanks in advance for doing that!

[Anonymous]

Hi @Newbie_SQLPower ,

In addition to lbendlin's reply, Power BI doesn't support us to loop by DAX. I suggest you to try M code in Power Query to get looping calculation.

For reference:

List.Generate() and Looping in PowerQuery

Power Query Loop Calculation

Easy Looping in Power BI/Power Query

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[lbendlin]

Power BI is the wrong tool for that. It has no concept of loops.