Relative reference in DAX creating a column - it works but I need to complicate it a bit

[grggmrtn]

RIght now my code looks like this:

Destination = 
IF (
    'Sankey'[Person ID]
        = LOOKUPVALUE (
            'Sankey'[Person ID];
            'Sankey'[Index]; 'Sankey'[Index] +1
        );
    'Sankey'[Source];
    "Stopped"
)

Which should translate to something like

"If [Person ID] in this index row and the next one are the same, then return [Source], else "Stopped"." It works, in any case.

But what I NEED is:

"If [Person ID] in this index row and the next one are the same, then return [Source], else "Stopped", unless [DATE] = TODAY(), then return [Source] anyway."

I would really appreciate any help I can get 🙂 

[v-yulgu-msft]

Hi @grggmrtn,

Destination =
IF (
    'Sankey'[Person ID]
        = LOOKUPVALUE ( 'Sankey'[Person ID]; 'Sankey'[Index]; 'Sankey'[Index] + 1 )
        && 'Sankey'[Date] = TODAY ();
    'Sankey'[Source];
    "Stopped"
)

Best regards,

Yuliana Gu

[grggmrtn]

Hey @v-yulgu-msft thanks!

Your code doesn't quite work though. I tend to explain things wrong 😉 .

The result of your code gives me 'Sankey'{Source] where [Person ID] is the same in both [Index] columns AND where [Date] = TODAY () - meaning both criteria have to be met. 

But what I need is for [Source] to be in the column where [Person ID] is the same in both [Index] columns, else "Stopped" UNLESS [Date] = TODAY (), then [Source].

Am I explaining the difference ok?

Here's an example of what I need, if we assume 08-02-2019 = Today():

Person ID	Source		Date		Destination
1		Support		02-10-2018	Training (Value from [Source] from the next row)
1		Training	08-02-2019	Training ([Person ID] is same, [Date]=Today())
2		Training	29-01-2019	Housing (Value from [Source] from the next row)
2		Housing		08-02-2019	Housing ([Person ID] is same, [Date]=Today())
3		Housing		07-08-2010	Housing (Value from [Source] from the next row)
3		Housing		05-02-2018	Stopped (because even though [Person ID] is same, [Date]<>Today ())
4		Vehicle		31-12-9999	Stopped (because [Person ID] is not same)
5		Meeting		12-10-2015	Stopped (because [Person ID] is not same)

while your code gave me:

Person ID	Source		Date		Destination
1		Support		02-10-2018	Stopped
1		Training	08-02-2019	Training
2		Training	29-01-2019	Stopped
2		Housing		08-02-2019	Housing
3		Housing		07-08-2010	Stopped
3		Housing		05-02-2018	Stopped
4		Vehicle		31-12-9999	Stopped 
5		Meeting		12-10-2015	Stopped 

Does that make any more sense?

[grggmrtn · Accepted solution]

Holy crap I got it to work. Combination of M and DAX.

First I added another index column in M, then merged the two index columns creating a base Destination column shifted from my source column

Then in DAX I used a nested IF to give me what I needed:

Destination  = 
IF (
'Sankey'[Person ID]
	= LOOKUPVALUE (
		'Sankey'[Person ID];
		'Sankey'[Index]; 'Sankey'[Index] + 1;
    'Sankey'[Destination];
    IF (
        'Sankey'[Date] = TODAY ();
        'Sankey'[Source];
        "Stopped"
    )
)

I'm still working on verifying my data, but this seems to work just like I needed it to.