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

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

      Hi, Anonymous 

       

      You may create a calculated table as below, modify the measure and use the columns from the table to display the result.

      New Table = 
UNION(
    'Table',
    ROW("Step No",9,"Depth from",BLANK(),"Depth to",BLANK())
)

       

       

      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 Anonymous ,

         

        As mentioned by v-alq-msft . Create  a Calculated Table

         

        Incase you want  measures instead of Calculated Column to five you the values.

         

        Total Depthm Measure =
        SWITCH(
        TRUE(),
        SELECTEDVALUE('Table'[stepNo]) = 1 , MAX('Table'[Depth from m]),
        SELECTEDVALUE('Table'[stepNo]) = 2, MAX('Table'[depth to m]),
        CALCULATE(MAX('Table'[depth to m]),FILTER(ALL('Table'),'Table'[stepNo] = MAX('Table'[stepNo]) - 1))
        )
         
        Regards,
        Harsh Nathani