Earlier function

[kizofilax]

Hello, I am trying to get the columns on the right using the first 2 columns

[TABLE="width: 500"]
<tbody>[TR]
[TD]Case[/TD]
[TD]Person[/TD]
[TD]From (need this)[/TD]
[TD]To (need this)[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]bob[/TD]
[TD]bob[/TD]
[TD]Kelly[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]kelly[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]jason[/TD]
[TD]jason[/TD]
[TD]russ[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]russ[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]bob[/TD]
[TD]bob[/TD]
[TD]miriam[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]miriam[/TD]
[TD]miriam[/TD]
[TD]carl[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]carl[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

That's what I am trying to accomplish, for each case basically which person started it and then who finished it. I am able to do it in Excel by referencing the previous cell but i can't figure it out in powerPivot using DAX. I have tried EARLIER like crazy but cannot get it to work

Any ideas?

Thanks

 

[JavierGuillen]

If you add an extra column to your table as an auto-increment number, you can achieve the result you expect with two calculated columns:

[From]:
=IF(Table1[ID] <> CALCULATE( MAX( Table1[ID] ), ALLEXCEPT( Table1, Table1[Case] ) ),
Table1[Person]
)

[To]:
=IF(Table1[ID] <>
CALCULATE( MAX( Table1[ID] ),
ALLEXCEPT( Table1, Table1[Case] )
),
CALCULATE(
LASTNONBLANK( Table1[Person], 1 ) ,
FILTER( Table1,
Table1[ID] = EARLIER( Table1[ID] ) + 1 )
)
)

 

[Laurent C]

My suggestion:

[To]
=CALCULATE(
FIRSTNONBLANK(Table1[Person],Table1[Person])
, CALCULATETABLE(
TOPN(1,Table1,Table1[SequenceID],1)
, ALLEXCEPT(Table1,Table1[Case])
, Table1[SequenceID] > EARLIER(Table1[SequenceID])
)
)

As already mentioned by Javier, you will need an information to find out in which order the actions occurred. I called mine SequenceID. If you have a date / time information, you can use that information instead. The CALCULATETABLE(TOPN ...)) expression finds the next action for this Case. The expression in the CALCULATE statement then returns the value in the [Person] column for this row.

[From]
=IF([To]<>BLANK(),[Person])

This assumes that [Person] is never blank in your data. A more robust version would be:

=IF(CALCULATE(
COUNTROWS(Table1)
, ALLEXCEPT(Table1,Table1[Case])
, Table1[SequenceID] > EARLIER(Table1[SequenceID])
)>0, [Person]
)

 

[kizofilax]

Thanks guys, I actually used the formula with LASTNONBLANK and for the order I used a date-time column in the EARLIER statement

 

[kizofilax]

Hello, I am trying to get the columns on the right using the first 2 columns

[TABLE="width: 500"]
<tbody>[TR]
[TD]Case[/TD]
[TD]Person[/TD]
[TD]From (need this)[/TD]
[TD]To (need this)[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]bob[/TD]
[TD]bob[/TD]
[TD]Kelly[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]kelly[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]jason[/TD]
[TD]jason[/TD]
[TD]russ[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]russ[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]bob[/TD]
[TD]bob[/TD]
[TD]miriam[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]miriam[/TD]
[TD]miriam[/TD]
[TD]carl[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]carl[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

That's what I am trying to accomplish, for each case basically which person started it and then who finished it. I am able to do it in Excel by referencing the previous cell but i can't figure it out in powerPivot using DAX. I have tried EARLIER like crazy but cannot get it to work

Any ideas?

Thanks

So I recently ran into an issue here, each row has a date
However some of the cases are not in continuous order, Javier's formula works but only if the ID is continuous. I tried Laurent's approach but it gave me a memory error lol (not enough memory due to too many errors)

This is how it looks raw and with the current formula for case 2 on the 10/12 we get it went to no one because the next record down has a different case ID. However on
[TABLE="width: 500"]
<tbody>[TR]
[TD]Date[/TD]
[TD]Case[/TD]
[TD]Person[/TD]
[TD]From (need this)[/TD]
[TD]To (need this)[/TD]
[/TR]
[TR]
[TD]10/09[/TD]
[TD]1[/TD]
[TD]bob[/TD]
[TD]bob[/TD]
[TD]Kelly[/TD]
[/TR]
[TR]
[TD]10/10[/TD]
[TD]1[/TD]
[TD]kelly[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]10/11[/TD]
[TD]2[/TD]
[TD]jason[/TD]
[TD]jason[/TD]
[TD]russ[/TD]
[/TR]
[TR]
[TD]10/12[/TD]
[TD]2[/TD]
[TD]russ[/TD]
[TD]russ[/TD]
[TD]renee[/TD]
[/TR]
[TR]
[TD]10/13[/TD]
[TD]3[/TD]
[TD]bob[/TD]
[TD]bob[/TD]
[TD]miriam[/TD]
[/TR]
[TR]
[TD]10/14[/TD]
[TD]3[/TD]
[TD]miriam[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]10/15[/TD]
[TD]2[/TD]
[TD]renee[/TD]
[TD]renee[/TD]
[TD]kelly[/TD]
[/TR]
[TR]
[TD]10/16[/TD]
[TD]2[/TD]
[TD]kelly[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


Need the following end result:
As you can see the goal will be to be able to order

[TABLE="width: 500"]
<tbody>[TR]
[TD]Date[/TD]
[TD]Case[/TD]
[TD]Person[/TD]
[TD]From (need this)[/TD]
[TD]To (need this)[/TD]
[/TR]
[TR]
[TD]10/09[/TD]
[TD]1[/TD]
[TD]bob[/TD]
[TD]bob[/TD]
[TD]Kelly[/TD]
[/TR]
[TR]
[TD]10/11[/TD]
[TD]2[/TD]
[TD]jason[/TD]
[TD]jason[/TD]
[TD]russ[/TD]
[/TR]
[TR]
[TD]10/12[/TD]
[TD]2[/TD]
[TD]russ[/TD]
[TD]russ[/TD]
[TD]renee[/TD]
[/TR]
[TR]
[TD]10/13[/TD]
[TD]3[/TD]
[TD]bob[/TD]
[TD]bob[/TD]
[TD]miriam[/TD]
[/TR]
[TR]
[TD]10/14[/TD]
[TD]3[/TD]
[TD]miriam[/TD]
[TD]miriam[/TD]
[TD]carl[/TD]
[/TR]
[TR]
[TD]10/15[/TD]
[TD]2[/TD]
[TD]renee[/TD]
[TD]renee[/TD]
[TD]kelly[/TD]
[/TR]
</tbody>[/TABLE]



So i need a way to first filter the table to just grab everything with the same Case ID and then probably the formula will work

Any ideas guys?/

Thanks!