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

     

amitchandak's avatar
amitchandak
Icon for Super User rankSuper User
6 years ago

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's avatar
Anonymous
Not applicable
6 years ago

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's avatar
    v-deddai1-msft
    Icon for Community Support rankCommunity Support
    6 years ago

    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'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