Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
oscargushiken
Frequent Visitor

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

DATEEMPLOYEE_IDJOB_CODEIndexIndex.1
2/1/202186753092902000301
11/15/201986753092902000212
3/21/201377793112903000423
5/11/200577793112903000334
3/24/200260608422901000245
2/1/200160608422901000156

 

AFTER (DESIRED)

DATEEMPLOYEE_IDJOB_CODEIndexIndex.1PREV_JOB_CODE
2/1/20218675309290200030129020002
11/15/201986753092902000212null
3/21/20137779311290300042329030003
5/11/200577793112903000334null
3/24/20026060842290100024529010001
2/1/200160608422901000156 

 

Your help in this would be amazing!

 

Thanks!

2 ACCEPTED SOLUTIONS
v-rongtiep-msft
Community Support
Community Support

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
    )
)

vpollymsft_0-1670897882472.png

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.

 

View solution in original post

Ashish_Mathur
Super User
Super User

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.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

5 REPLIES 5
Ashish_Mathur
Super User
Super User

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.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

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

You are welcome.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
v-rongtiep-msft
Community Support
Community Support

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
    )
)

vpollymsft_0-1670897882472.png

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.

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.