Deep lookup over column pair

[Anonymous]

Let's simplify dataset on which I'm working to 3 columns - step_id,  previous_step, meta. What I want to achieve is to fill in meta of any row lacking this value with meta of any entry in a certain step chain (i.e. any meta from group of steps that has the same "earliest" step). I assume that the metadata is always the same for all entries created by a certain step (so selecting which step to base new meta on is not an issue).

I was thinking on adding a column for the "earliest" step in certain chain, so that I could use something like FIRSTNONBLANK() over the filtered table, but had no luck doing it with DAX and I lack PowerQuery fluency to develop it myself.

Is this doable in PowerQuery or, preferably, DAX?

This is a sample of what I would want to achieve:

step_id	previous_step	meta	earliest	meta_filled
1	blank	blank	1	US
2	1	blank	1	US
3	1	US	1	US
4	blank	blank	4	blank
5	blank	blank	5	UK
6	5	blank	5	UK
7	6	UK	5	UK
8	7	blank	5	UK

[amitchandak]

@Anonymous ,The information you have provided is not making the problem clear to me. Can you please explain with an example.

Appreciate your Kudos.

[Anonymous]

My dataset is a list of processing steps that are, at the system from which the data originates, grouped into processings, however tha only remnant of this is th column previous_step - it is blank if a step is the first one of its processing and it points to a previous one if not. 

I want to be able to lookup the earliest step in a processing for each single step, so that I'm able to group them back into processings, so that values from other columns could be aggregated/looked up

[v-deddai1-msft]

Hi @Anonymous ，

I still can't get the expected output you want from your description. Would you please show us more details and expected output based on your sample data?

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

Best Regards,

Dedmon Dai

[Anonymous]

Let's generalize it further.

Input:

step_id	previous_step
1
2	1
3	2
4
5	4
6	4
7	6
8	3

Output:

step_id	previous_step	first_step
1		1
2	1	1
3	2	1
4		4
5	4	4
6	4	4
7	6	4
8	3	1

[v-deddai1-msft · Accepted solution]

Hi @Anonymous ,

Is the screenshot your expected output?

Just create a calculated column for getting the first step:

Column = IF('Table'[previous_step] = BLANK(),'Table'[step_id],BLANK())

Then use dax to fill down:

first_step =
VAR LastNonBlankStep =
    CALCULATE (
        LASTNONBLANK ( 'Table'[step_id], 1 ),
        FILTER (
            ALL ( 'Table' ),
            'Table'[step_id] <= EARLIER ( 'Table'[step_id] )
                && NOT ( ISBLANK ( 'Table'[Column] ) )
        )
    )
RETURN
    CALCULATE (
        MAX ( 'Table'[Column] ),
        FILTER ( ALL ( 'Table' ), 'Table'[step_id] = LastNonBlankStep )
    )

For more details, please refer to the pbix file:https://qiuyunus-my.sharepoint.com/:u:/g/personal/pbipro_qiuyunus_onmicrosoft_com/ERXKTCCXNOVCrS1Fwsw6W4IB91H3Ib8IaZDbBvb8hn6uYg?e=hKvZOS

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

Best Regards,

Dedmon Dai