Forum Discussion
Conditional Merge Queries based on column value
Dear forum,
Would would be the best way to merge data from a bottom row based on meeting a specific column value?
I was attmepting to do this using two offset Index columns and then execute a normal Merge Queries. However, I am at a loss as to how to make this conditionally based on matching the EMPLOYEE_IDs.
The end product shoule be rows of data where the current and the previous (bottom row) Job_Code are on the same line for each Employee_ID. If I do a normal Merge Queries without filtering for a matching Employee_ID, then at somepoint I will be bringing up Job_Codes that don't belong to the employee in question. I would rather populate "0" or null if there is no match.
BEFORE
| DATE | EMPLOYEE_ID | JOB_CODE | Index | Index.1 |
| 2/1/2021 | 8675309 | 29020003 | 0 | 1 |
| 11/15/2019 | 8675309 | 29020002 | 1 | 2 |
| 3/21/2013 | 7779311 | 29030004 | 2 | 3 |
| 5/11/2005 | 7779311 | 29030003 | 3 | 4 |
| 3/24/2002 | 6060842 | 29010002 | 4 | 5 |
| 2/1/2001 | 6060842 | 29010001 | 5 | 6 |
AFTER (DESIRED)
| DATE | EMPLOYEE_ID | JOB_CODE | Index | Index.1 | PREV_JOB_CODE |
| 2/1/2021 | 8675309 | 29020003 | 0 | 1 | 29020002 |
| 11/15/2019 | 8675309 | 29020002 | 1 | 2 | null |
| 3/21/2013 | 7779311 | 29030004 | 2 | 3 | 29030003 |
| 5/11/2005 | 7779311 | 29030003 | 3 | 4 | null |
| 3/24/2002 | 6060842 | 29010002 | 4 | 5 | 29010001 |
| 2/1/2001 | 6060842 | 29010001 | 5 | 6 |
Your help in this would be amazing!
Thanks!
Hi oscargushiken ,
I have created a simple sample, please refer to it to see if it helps you.
Create a measure.
result = CALCULATE ( MAX ( 'date'[JOB_CODE] ), FILTER ( ALL ( 'date' ), 'date'[EMPLOYEE_ID] = SELECTEDVALUE ( 'date'[EMPLOYEE_ID] ) && 'date'[Index] = SELECTEDVALUE ( 'date'[Index] ) + 1 ) )Or a column.
Column = CALCULATE ( MAX ( 'date'[JOB_CODE] ), FILTER ( ALL ( 'date' ), 'date'[EMPLOYEE_ID] = EARLIER ( 'date'[EMPLOYEE_ID] ) && 'date'[Index] = EARLIER ( 'date'[Index] ) + 1 ) )How to Get Your Question Answered Quickly
If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .
Best Regards
Community Support Team _ Polly
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.Hi,
This calculated column formula works
Column = LOOKUPVALUE(Data[JOB_CODE],Data[DATE],CALCULATE(MAX(Data[DATE]),FILTER(Data,Data[EMPLOYEE_ID]=EARLIER(Data[EMPLOYEE_ID])&&Data[DATE]<EARLIER(Data[DATE]))),Data[EMPLOYEE_ID],Data[EMPLOYEE_ID])Hope this helps.
5 Replies
Hi oscargushiken ,
I have created a simple sample, please refer to it to see if it helps you.
Create a measure.
result = CALCULATE ( MAX ( 'date'[JOB_CODE] ), FILTER ( ALL ( 'date' ), 'date'[EMPLOYEE_ID] = SELECTEDVALUE ( 'date'[EMPLOYEE_ID] ) && 'date'[Index] = SELECTEDVALUE ( 'date'[Index] ) + 1 ) )Or a column.
Column = CALCULATE ( MAX ( 'date'[JOB_CODE] ), FILTER ( ALL ( 'date' ), 'date'[EMPLOYEE_ID] = EARLIER ( 'date'[EMPLOYEE_ID] ) && 'date'[Index] = EARLIER ( 'date'[Index] ) + 1 ) )How to Get Your Question Answered Quickly
If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .
Best Regards
Community Support Team _ Polly
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.Thanks v-polly-msft, the .pbix file helps a lot and works like a charm.
Hi,
This calculated column formula works
Column = LOOKUPVALUE(Data[JOB_CODE],Data[DATE],CALCULATE(MAX(Data[DATE]),FILTER(Data,Data[EMPLOYEE_ID]=EARLIER(Data[EMPLOYEE_ID])&&Data[DATE]<EARLIER(Data[DATE]))),Data[EMPLOYEE_ID],Data[EMPLOYEE_ID])Hope this helps.
Thank you Ashish - this solution worked. I was so fixated on using Power Query, that I was not considering DAX.
You are welcome.
