4 Replies Latest reply on Jul 8, 2016 8:50 AM by Justin Stanley

    Find Last Corresponding Values in Current Table

    Justin Stanley

      I have a data set that looks something like this:

       

      ItemID
      DateKey
      11116/1/2016Monkeys
      22226/1/2016
      33336/1/2016
      11116/2/2016Monkeys
      22226/2/2016Frogs
      33336/2/2016
      11116/3/2016Monkeys
      22226/3/2016Frogs
      33336/3/2016Bears
      11116/4/2016Monkeys
      33336/4/2016Bears

       

       

      And I need to figure out how to add what I assume would be a calculated column that, for each row, shows the last available for that key. So, starting off with that dataset, the result would be something like this:

       

      ItemID
      DateKeyMostRecentKey
      11116/1/2016MonkeysMonkeys
      22226/1/2016Frogs
      33336/1/2016Bears
      11116/2/2016MonkeysMonkeys
      22226/2/2016FrogsFrogs
      33336/2/2016Bears
      11116/3/2016MonkeysMonkeys
      22226/3/2016FrogsFrogs
      33336/3/2016ZebrasBears
      11116/4/2016MonkeysMonkeys
      33336/4/2016BearsBears

       

       

      And for the life of me, I can't figure out how to do it in Tableau. In Excel/DAX, I can get the result with something like this:

       

      =CALCULATE(LASTNONBLANK(MyTable[Key],1), FILTER(MyTable, [ItemID] = EARLIER(MyTable[Key])))

       

      But I'm pretty new to Tableau and have no idea what an equivalent function would look like. Any ideas?