RECURSIVELY FINDING ROWS

[Anonymous]

Hi everyone!

I am trying to show rows recursively in my table visual. The table is controlled by a slicer. My data looks like this:

I am using a slicer to show values in the table, the slicer is associated with the first family value `G3MS`. So only first row is getting displayed in the visual. I want to recursively search for subsequent replacements and display the rows as shown.

[Anonymous]

@Greg_Deckler , a great solution in Power BI Desktop with DAX.

I reviewed this case was created in Power Query forum, so I would like to provide a solution in Power Query.

Here's the steps:

1.Add an index column.

2.Add a custom column.

3.Expand the values and you can see the result.

Reference:Value from previous row – Power Query, M language – Trainings, consultancy, tutorials (exceltown.com...

Best Regards,

Stephen Tao

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

[Anonymous]

But this index method may only work if there's a single sequence right? What if I have more families with corresponding replacements? 
Really appreciate your help! 

I used this function:
= (x as text)=>
let a= if List.PositionOf(ModuleList, Text.AfterDelimiter(x, ",", {0, RelativePosition.FromEnd}))<0
then x
else @GetHierarchy(x & "," & ReplacementList{List.PositionOf(ModuleList, Text.AfterDelimiter(x, ",", {0, RelativePosition.FromEnd}))})
in a

This seemed to solve the problem 80%

[Greg_Deckler]

@Anonymous Do you have anything that you can use to define "later" or "earlier" in your table, like a date or index? If so, you can use the MTBF pattern. See my article on Mean Time Between Failure (MTBF) which uses EARLIER: http://community.powerbi.com/t5/Community-Blog/Mean-Time-Between-Failure-MTBF-and-Power-BI/ba-p/339586.
The basic pattern is:
Column = 
  VAR __Current = [Value]
  VAR __PreviousDate = MAXX(FILTER('Table','Table'[Date] < EARLIER('Table'[Date])),[Date])

  VAR __Previous = MAXX(FILTER('Table',[Date]=__PreviousDate),[Value])
RETURN
 ( __Current - __Previous ) * 1.

[Anonymous]

I do have date columns (the date on which the family was replaced). I will try this solution out! 

Thanks!!