Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Custom column

Hi, so I have two columns depth from and depth to. I need to create a new column called Total depth that: - has the first row the same as in depth from (30 in this case) - the rest of rows are copi...
Need Help
v-alq-msft's avatar
v-alq-msft
Community Support
6 years ago

Hi, Anonymous 

 

Based on your description, you need to create an index column in 'Query Editor'. The pbix file is attached in the end.

 

Then you may create a calculated column and a measurem, or only a measure.

Calculated column:

Column = 
IF(
    [Index]=0,
    [Depth from],
    LOOKUPVALUE('Table'[Depth to],'Table'[Index],[Index]-1)
)

Measure:
Measure 1 = 
IF(
    ISFILTERED('Table'[Depth from]),
    SUM('Table'[Column]),
    LOOKUPVALUE('Table'[Depth to],'Table'[Index],CALCULATE(MAX('Table'[Index]),ALL('Table')))
)

 

or

 

Measure 2 = 
var _index = SELECTEDVALUE('Table'[Index])
return
IF(
    ISFILTERED('Table'[Depth from]),
    IF(
        _index=0,
        SELECTEDVALUE('Table'[Depth from]),
        LOOKUPVALUE('Table'[Depth to],'Table'[Index],_index-1)
    ),
    LOOKUPVALUE('Table'[Depth to],'Table'[Index],CALCULATE(MAX('Table'[Index]),ALL('Table')))
)

 

Result:

 

Best Regards

Allan

 

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

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Hi v-alq-msft  Sorry, forgot to include this column called step no:

     

    • v-alq-msft's avatar
      v-alq-msft
      Community Support
      6 years ago

      Hi, Anonymous 

       

      You may create a measure as below. The pbix file is attached in the end.

      Result = 
var _stepno = SELECTEDVALUE('Table'[Step No])
return
IF(
    ISFILTERED('Table'[Depth from]),
    IF(
        _stepno=1,
        SELECTEDVALUE('Table'[Depth from]),
        LOOKUPVALUE('Table'[Depth to],'Table'[Step No],_stepno-1)
    ),
    LOOKUPVALUE('Table'[Depth to],'Table'[Step No],CALCULATE(MAX('Table'[Step No]),ALL('Table')))
)

       

      Result:

       

      Best Regards

      Allan

       

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

      • Anonymous's avatar
        Anonymous
        Not applicable
        6 years ago

        Hi v-alq-msft I would need that last value from the Depth to (500) to be at the bottom in the Result column. Can we add step 9 in the step_no column and have 500 value in the Depth to in the same row?