Custom column

[Anonymous]

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 copies from the depth to column.

[harshnathani]

Hi @Anonymous ,

Create a Calculated Column

Total Depthm =

SWITCH(

TRUE(),

'Table'[stepNo] = 1 , 'Table'[Depth from m],

'Table'[stepNo] = 2, 'Table'[depth to m],

CALCULATE(MAX('Table'[depth to m]),FILTER('Table','Table'[stepNo] < EARLIER('Table'[stepNo]))

) )

Regards,
Harsh Nathani

Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!! (Click the Thumbs Up Button)

[Anonymous]

Hi @harshnathani thanks!

There are, however, to issues with this column:

- md_to3 and md_to4 should be equal to Total Depthm4 and Total Depthm5 respectively

- md_to16 (9726,71) is not copied/added to the Total Depthm column.

[harshnathani]

Hi @Anonymous ,

Make sure that the values are in Don't Summarize form

Use this formula for Calculated Column

Total Depthm =

SWITCH(

TRUE(),

'Table'[stepNo] = 1 , 'Table'[Depth from m],

'Table'[stepNo] = 2, 'Table'[depth to m],

CALCULATE(MAX('Table'[depth to m]),FILTER('Table','Table'[stepNo] = EARLIER('Table'[stepNo]) - 1))

)

Also add a blank row will step no to have the last value shown.

Regards,
Harsh Nathani

Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!! (Click the Thumbs Up Button)

[Anonymous]

@harshnathani What if I wouldn't like to change the original data in Query. Can it be done via measure/column in report visualization?

[v-alq-msft]

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]

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

[v-alq-msft]

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]

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]

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]

@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]

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]

@v-alq-msft the problem is that the number of steps can vary, this time it's 9 but it can be only 3 or 30 next time.

[harshnathani]

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

[Anonymous]

@harshnathani  the problem is that the number of steps can vary, this time it's 9 but it can be only 3 or 30 next time.

[harshnathani]

Hi @Anonymous ,

One solution could be to  do it QueryEditor.    to create a Table with 1 row in Query Editor. Give that an index value 10000000. And then append this table to the Steps table.   This will automate and you will not need to worry till your index in Steps Table reaches 10000000.  

For more info I will need to see your data set.  

Regards,

Harsh Nathani

[Tahreem24]

@Anonymous ,

Follow the below steps:

Step 1: create index column from Power Query. Go to Power Query editor --> Select any column on which you want to create index lets say "DateColumn"  --> Go to Add Column menu --> click on Index Column --> Save and apply.

Step 2: Create below DAX Column:

Column =

Var a = CALCULATE(MAX(Sheet6[Value2]),FILTER((Sheet6),Sheet6[Index]=EARLIER(Sheet6[Index])-1))

RETURN IF(a=BLANK(),CALCULATE(SUM(Sheet6[Value]),Sheet6[Index]=0),a)

See the below Screen shot:

[Tahreem24]

So directly use the calculated column like below:

Column =

Var a = CALCULATE(MAX(Sheet6[Value2]),FILTER((Sheet6),Sheet6[StepNo]=EARLIER(Sheet6[StepNo])-1))

RETURN IF(a=BLANK(),CALCULATE(SUM(Sheet6[Value]),Sheet6[StepNo]=1), a)

[Anonymous]

Hi @Tahreem24  Sorry, forgot in include this column called step no:

[parry2k]

@Anonymous you have to have an identifier to find the previous row, again as mentioned in the previous post, some index/id/date column to find out the row if you don't have this in the model you can add index column in power query.

Let's be clear, it is not as straight forward as excel where you can refer to a cell.

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!

⚡Visit us at https://perytus.com, your one-stop shop for Power BI related projects/training/consultancy.⚡

[Anonymous]

@parry2k Yes, here it is, sorry forgot to include it: