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

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

      Hi, Anonymous 

       

      You may add a row in 'Query Edior'. The pbix file is attached in the end.

       

      Then you can modify the meaure as below.

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

       

      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

        v-alq-msft measure is working fine, but I've two comments:

        - I wouldn't like to change the original data, so could you please suggest how to add this last row in the report vis?

        - I've noticed some inconsistency in the data so that I will need this logic in the formula: if a vlaue in the current row is less than a previous value in the row above, replace the current value with that of the row above.