Conditional Merge Queries based on column value

[oscargushiken]

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!

[v-rongtiep-msft · Accepted solution]

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.

[Ashish_Mathur · Accepted solution]

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.

[oscargushiken]

Thank you Ashish - this solution worked.  I was so fixated on using Power Query, that I was not considering DAX. 

[Ashish_Mathur]

You are welcome.

[v-rongtiep-msft · Accepted solution]

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.

[oscargushiken]

Thanks v-polly-msft, the .pbix file helps a lot and works like a charm.