Forum Discussion
Recursion in Power BI
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?
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.
1 Reply
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.