Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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 🙂
Solved! Go to 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.
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
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?
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.
User | Count |
---|---|
92 | |
77 | |
71 | |
64 | |
58 |
User | Count |
---|---|
106 | |
97 | |
76 | |
63 | |
61 |