Forum Discussion

roxyavgfli's avatar
roxyavgfli
New Member
8 years ago

Need help with a custom column

Hi guys,

 

I'm currently having trouble with a custom column that I'm trying to build.

here is a picture of the result I try to obtain:

t

The idea is :
General rule =

by(id, timestamp){

- always keep the latest val2

- the first time we meet a val 1 copy the value on all previous expected result column (with the same ID)

- if there is no val2, keep the val3

}

The data is built this way:

the last val2 is always equal to val3

It is possible not to have either val2 or val3 for a given ID

you can't have a val2 without val1 or a val1 without val2.

 

Hope this is understable.

 

Any help would be very very appreciated.

 

Thank you in advance guys...

 

1 Reply

  • v-danhe-msft's avatar
    v-danhe-msft
    Microsoft Employee
    8 years ago

    Hi roxyavgfli,

    Based on my test, you can refer to below steps:

    1.I have entered some sample data.

    2.Add a customer column to copy [val1] column and use the fill up function in query editor.

    3.Create three calculated columns. Now you can see the correct result.

    First column = IF('Table1'[val2]<>BLANK(),[val2],IF('Table1'[val1]=BLANK(),'Table1'[val3]))

    Middle column = IF([val1]=BLANK(),[Custom],'Table1'[First column])

    Result column = IF('Table1'[Middle column]<>BLANK(),Table1[Middle column],[First column])

    You can also download the PBIX file to have a view.

    https://www.dropbox.com/s/wpi0xqwp03nwx5q/Need%20help%20with%20a%20custom%20column.pbix?dl=0

     

    Regards,

    Daniel He