Forum Discussion

damonkimble's avatar
damonkimble
Frequent Visitor
5 years ago
Solved

Timestamps duplicate rows - break into columns

Hello all,

I am running into a small issue where the column "TimeStamp Traveling" has two booking statuses "traveling" where I want them to have their own columns. So one column to have traveling #1 and another column traveling#2. Is this doable based on the current data? Thank you.

 

 

Need Help
Tips and Tricks
  • v-yalanwu-msft's avatar
    v-yalanwu-msft
    5 years ago

    Hi damonkimble  ,  

    IF you want to two different column ,may you should create two column as follows: 

    Traveling#1 = var _rank=IF([TimeStamp Taveling]<>BLANK(),RANKX('Table',[TimeStamp Taveling]))
return IF(_rank=1,[TimeStamp Taveling])
    Traveling#2 = 
var _rank=IF([TimeStamp Taveling]<>BLANK(),RANKX('Table',[TimeStamp Taveling]))
return IF(_rank=2,[TimeStamp Taveling])

    The final output is shown below:  

    Best Regards,
    Community Support Team_ Yalan Wu
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.   

4 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    5 years ago

    damonkimble , Create a new column

    Switch( true(),

    not(isblank([TimeStamp Completed])) ,"Completed",

    not(isblank([TimeStamp Traveling])) ,"Traveling",

    "In progress"

    )

    • damonkimble's avatar
      damonkimble
      Frequent Visitor
      5 years ago

      Hi amitchandak , the code you sent is equivalent to the booking status column. What I wanted to know is there are two different timestamps for the "Traveling" booking status - where I want one column called Traveling#1 with one of the timestamp and Traveling #2 with the other timestamp instead of having in one column. Thank you either way!

  • v-yalanwu-msft's avatar
    v-yalanwu-msft
    Community Support
    5 years ago

    Hi damonkimble  ,  

    IF you want to two different column ,may you should create two column as follows: 

    Traveling#1 = var _rank=IF([TimeStamp Taveling]<>BLANK(),RANKX('Table',[TimeStamp Taveling]))
return IF(_rank=1,[TimeStamp Taveling])
    Traveling#2 = 
var _rank=IF([TimeStamp Taveling]<>BLANK(),RANKX('Table',[TimeStamp Taveling]))
return IF(_rank=2,[TimeStamp Taveling])

    The final output is shown below:  

    Best Regards,
    Community Support Team_ Yalan Wu
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.   

    • damonkimble's avatar
      damonkimble
      Frequent Visitor
      5 years ago

      Good Morning v-yalanwu-msft ,

      Thank you! Quick question, TimeStamp Traveling is actually a customized column where if the booking status = "Traveling" then get the timestamps. 

       

      I tried creating the columns Traveling#1 and #2 but nothing is displaying. I also tried to redefine the TimeStamp Traveling column, but no result.

       

      Here is the dax code for TimeStamp Traveling before modifying:

      IF(bookingtimestamp[booking status]="Traveling", bookingtimestamp[timestamp])

       

       

      Here is the modification I made:

      TimeStamp Traveling = var _rank=IF(msdyn_bookingtimestamp[booking status]="Traveling" && [timestamptime]<> BLANK(), RANKX('msdyn_bookingtimestamp', [timestamptime]))
      return IF(_rank=1,[timestamptime])