Custom column measures

[Anonymous]

Hi everyone, I am probably doing something wrong here...

I have a table that contains these 5 columns on the left (the picture below). So I've created (with your help) two measures called TimeM and DepthM from original columns TimeFrom/TimeTo and DepthFrom/DepthTo respectively. So it basically copies first rows from TimeFrom and DepthFrom into the corresponding measures, and copies entire columns TimeTo and DepthTo, all of them are filtered by the step_no. The problem is that during this transformation the last row from both TimeTo (value 😎 and DepthTo (value 10000) is not visible in TimeM and DepthM visual as there no steps after step_no 16. 

How can I plot these two measures TimeM and DepthM without missing the last row from original columns? I am not allowed to add more rows to the step_no column, the order and number of steps should stay the same. What about creating an extra column that is related to the step_no and can be extended to include 17 rows?

[ryan_mayu]

@Anonymous 

maybe you can try create a new table for this

newtable = 
VAR first=SELECTCOLUMNS(FILTER('Table','Table'[step_no]=min('Table'[step_no])),"stop_no",'Table'[step_no],"TimeM",'Table'[timefrom],"DepthM",'Table'[depthfrom])
VAR middle=SELECTCOLUMNS(FILTER('Table','Table'[step_no]>min('Table'[step_no])),"stop_no",'Table'[step_no],"TimeM",MAXX(FILTER('Table','Table'[step_no]=EARLIER('Table'[step_no])-1),'Table'[timeto]),"DepthM",MAXX(FILTER('Table','Table'[step_no]=EARLIER('Table'[step_no])-1),'Table'[depthto]))
VAR last=SELECTCOLUMNS(FILTER('Table','Table'[step_no]=max('Table'[step_no])),"stop_no","","TimeM",'Table'[timeto],"DepthM",'Table'[depthto])
return union(first,middle,last)

I only used 10 rows of your sample data

[Anonymous]

@ryan_mayu would I be able to plot DepthM vs TimeM then? 

I've also run into this:

The expression refers to multiple columns. Multiple columns cannot be converted to a scalar value.

[ryan_mayu]

@Anonymous 

Yes, I think so, that was a new table. could you please share more details of plotting depthM and timeM?

[amitchandak]

@Anonymous ,

timeM = maxx(filter(Table, [step_no] =earlier([step_no]) -1),[Time_no])

Depth M seems same as Depth from ?

[Anonymous]

Hi @amitchandak thanks! couple of questions about this expression:

1. what is [Time_no] ?

2. can it be converted into a column instead?

Regarding Depth M, it is a copy of the column DepthTo starting from the second row down (first row is from the colmn DepthFrom).

Hope it makes it more understandable.

[Anonymous]

@amitchandak DepthM rows starting from the second are copies of rows from the DepthTo