Recursion in Power BI

[mat33delo]

Hello All, 

I'm brainstorming ideas on how to run recursion in Power BI and wanted community input.

High level, I need to display all the orders of a specific item/lot-number but this includes orders of this item and all the finishedgood BOMs that this lot is a part of. Unfortunately, the new Finished Good can be part of another completely different BOM. Thus recursion is necessary to out put all the relevant orders connected to a specific lot since this parent child relationship can go multiple levels. 

So I have a table of all orders and a table of these BOMs

I don't think I can do this in Power BI but I think I can figure this out using Python.

If I used python, how would I embed this solution into a report?

Ideally, the user would use a slicer and choose specific lot then the python visual would use recursion and display a table of all the relevant orders. The user can then download the report. 

Is this approach possible and are there any other potential solutions?

[rohit1991 · Accepted solution]

HI @mat33delo ,

Power BI does not natively support recursion, but you can achieve this using one of three methods:

1. DAX Approach (Limited Depth)

Use DAX Parent-Child Hierarchy functions to track relationships.

• Create a BOM path using PATH().
• Use PATHCONTAINS() to filter relevant orders dynamically.
  

  BOM_Path = PATH(BOM[Child_Item], BOM[Parent_Item])

  Is_Relevant_Order =
  IF(PATHCONTAINS(BOM[BOM_Path], SELECTEDVALUE(Orders[Lot_Number])), 1, 0)

• Works well for limited recursion depth but does not scale well for deep hierarchies.

2. Python Approach (Best for Deep Recursion)

Use Python inside Power BI to handle multi-level recursion dynamically.

• Add a Python Visual and write a recursive function:

• def find_orders(df, lot_number, bom_df):
  related_orders = set()
  def recurse(lot):
  children = bom_df[bom_df['Parent_Item'] == lot]['Child_Item'].tolist()
  related_orders.update(children)
  for child in children:
  recurse(child)

  recurse(lot_number)
  return df[df['Lot_Number'].isin(related_orders)]

  selected_lot = dataset['Lot_Number'].iloc[0] # Get selected slicer value
  result_df = find_orders(dataset, selected_lot, bom_data)
  result_df

3. Power Query (M) Approach (Precomputed Table for Performance)

If real-time recursion is not required, precompute the hierarchy using Power Query (M).

• Flatten the BOM table before loading into Power BI.
• Use joins and recursive logic in Power Query to structure relationships.
• Refresh the dataset when BOM changes.