Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Deep lookup over column pair

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 s...
  • v-deddai1-msft's avatar
    v-deddai1-msft
    6 years ago

    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

     

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Let's generalize it further.

 

Input:

step_idprevious_step
1 
21
32
4 
54
64
76
83

 

Output:

step_idprevious_stepfirst_step
1 1
211
321
4 4
544
644
764
831

 

 

v-deddai1-msft's avatar
v-deddai1-msft
Icon for Community Support rankCommunity Support
6 years ago

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