FIRSTNONBLANK by Date and ID

[Victor_V]

Hey all,

I'm currently working on a report for our marketing automation campaigns. I want to figure out on which campaign the most people start with their journey. The way my table is made up is like this,

Date	UserID	Campaign
01/01/2019	A	Z
08/01/2019	A	Y
04/01/2019	B	Y
06/01/2019	B	X

So in the case above user A starts the journey with campaign Z and user B starts with campaign Y. I tried to use the following formula to extract the first interaction: 

FirstInteraction = CALCULATE(FIRSTNONBLANK(Table[Campaign], 1), ALLEXCEPT(Table, Table[UserID]))

The problem is that this then returns campaign Y for user A and campaign X for user B, because it seems to look at the alphabetical order and not the date order in which they occured. I read that DAX apparently doesn't know the order in which your data is sorted.

Is there a way to alter the formula above (or a new one) so DAX knows to look for the earliest date of each User ID and return the campaign associated with it?

[Anonymous]

@Victor_V Please use below measure

Measure = 
VAR _latestdate = CALCULATE(MIN('Table'[Date]),ALLEXCEPT('Table','Table'[UserID]))
RETURN CALCULATE(FIRSTNONBLANK('Table'[Campaign],TRUE()),FILTER(ALLEXCEPT('Table','Table'[UserID]),'Table'[Date]=_latestdate))

[Victor_V]

@Anonymous It worked! Thanks a lot!