how to create a batch tree

[jps_HHH]

I have a table with multiple lines with the following info:

Line no.  |   Product |  Batch  |   Material 1 | Batch 1 | etc 
123                         A              B20             XD            B80          
143                         A              B21             XD            B82          
163                         A              B22             XD            B82          
456                        XD             B80             ED            C57

And I would like to create a batch tree. i.e. I want a table that is filtered by product/batch.  If I select product A and batch B20 , it appears the lines 123 and 456.  
the line 123 is the batch A - B20 manufactured and the line 456 is the line of batch XD -B80 that it was consumed to produce batch A - B20.
it is that possible ? 

[v-csrikanth]

Hi @jps_HHH 

We haven't heard from you since last response and just wanted to check whether the solution provided has worked for you. If yes, please Accept as Solution to help others benefit in the community.
Thank you.

If the above information is helpful, please give us Kudos and mark the response as Accepted as solution.
Best Regards,
Community Support Team _ C Srikanth.

[v-csrikanth]

Hi @jps_HHH 

I wanted to follow up since I haven't heard from you in a while. Have you had a chance to try the suggested solutions?
If your issue is resolved, please consider marking the post as solved. However, if you're still facing challenges, feel free to share the details, and we'll be happy to assist you further.
Looking forward to your response!

Best Regards,
Community Support Team _ C Srikanth.

[v-csrikanth]

Hi @jps_HHH 

It's been a while since I heard back from you and I wanted to follow up. Have you had a chance to try the solutions that have been offered?
If the issue has been resolved, can you mark the post as resolved? If you're still experiencing challenges, please feel free to let us know and we'll be happy to continue to help!
Looking forward to your reply!

Best Regards,
Community Support Team _ C Srikanth.

[v-csrikanth · Accepted solution]

Hi @jps_HHH 

Thanks for reaching out ot the fabric community.
Please do follow the below steps to resolve the issue.

1. Unpivot your material and batch columns into a parent-child relations table using:
   Table.UnpivotOtherColumns(
   RawBatches,
   {"Line no.","Product","Batch"},
   "MaterialProduct",
   "MaterialBatch"
   )
   ``` :contentReference[oaicite:0]{index=0}
2. Add ParentKey = [Product] & "|" & [Batch] and ChildKey = [MaterialProduct] & "|" & [MaterialBatch] columns to the relations table.
3. Create a blank query BatchTree and paste in an M script that:
   

   • Starts with a list {SelectedBatch}

   • Uses List.Generate to repeatedly find rows where ParentKey is in the current list, collect their ChildKeys, and stop when no new keys appear https://learn.microsoft.com/en-us/powerquery-m/list-generate
4. Filter the relations table to only rows whose ParentKey is in the final expanded key list.
5. Merge those filtered rows back to RawBatches on the matching key to retrieve full row details.
6. Expose SelectedBatch as a slicer (via a disconnected parameter table or What-If parameter) so users pick e.g. A|B20.
7. Bind your visual (table or matrix) to the BatchTree query—selecting a batch will now show it plus all downstream batches (XD|B80 → ED|C57 → CV|D90) at any depth.
   
   

If the above information is helpful, please give us Kudos and mark the response as Accepted as solution.
Best Regards,
Community Support Team _ C Srikanth.

[pankajnamekar25]

Hello @jps_HHH 

Use this DAX to create a calculated table

BatchTree =

UNION (

    SELECTCOLUMNS(MainTable, "ParentLine", [Line no.], "ChildLine", [Line no.]),

    SELECTCOLUMNS(

        FILTER(

            CROSSJOIN(MainTable, MainTable),

            MainTable[Material 1] = MainTable_1[Product]

            && MainTable[Batch 1] = MainTable_1[Batch]

        ),

        "ParentLine", MainTable_1[Line no.],

        "ChildLine", MainTable[Line no.]

    )

)

Thanks,
 Pankaj Namekar | LinkedIn

If this solution helps, please accept it and give a kudos (Like), it would be greatly appreciated.

[jps_HHH]

thanks for your reply. 
My batch tree could have 4 or 5 products. 
Example: 

Product A batch B20 is manufactured used Product XD, batch B80.
XD.B80 is manufactured used Product ED.C57.
ED.C57 is manufactured used Product CV.D90.

I would like to select product A and batch B20 and the system automatically filters 

XD.B80
ED.C57
CV.D90