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.
Hi, wondering if anyone can help with this,
I've got quite a mess of tables with meeting dates and trying to get my head round the time fram the user had to wait from going from one meeting to the next..
I have two tables, table1 and table2, linked by UserID, both have a MeetingDate and MeetingType column, what i need to do is get the date difference between MeetingDates for MeetingType A in table 1, and B in table2.
to complicate things further, Users might have had multiple meeting A and Meeting Bs, as after meeting b they can be referred back to meeting a... so for example the table might look like this for one user;
userID table1[MeetingDate] table1[MeetingType] table2[MeetingDate] table2[MeetingType]
1 01/01/2018 a 10/02/2015 c
1 01/02/2018 a 10/01/2018 b
1 01/04/2018 d 10/03/2018 b
In this example the column i would like to add would show there was 9 days difference between 01/01/2018 meeting a and the 10/01/2018, and 36 days between 01/02/2018 meeting a and 10/03/2018 meeting b
I just can't get my head round linking them up, any help would be much appreciated..
Solved! Go to Solution.
HI @mik618 .
Maybe you can try to use following measure to lookup second table for next date.
Measure = VAR currId = MAX ( Table1[userID] ) VAR currDate = MAX ( Table1[MeetingDate] ) RETURN CALCULATE ( MIN ( Table2[MeetingDate] ), FILTER ( ALLSELECTED ( Table2 ), [MeetingDate] > currDate && [userID] = currId ) )
Regards,
Xiaoxin Sheng
HI @mik618 .
Maybe you can try to use following measure to lookup second table for next date.
Measure = VAR currId = MAX ( Table1[userID] ) VAR currDate = MAX ( Table1[MeetingDate] ) RETURN CALCULATE ( MIN ( Table2[MeetingDate] ), FILTER ( ALLSELECTED ( Table2 ), [MeetingDate] > currDate && [userID] = currId ) )
Regards,
Xiaoxin Sheng
Hi Xiaoxin,
Thanks so much for this, looks like this is is what i need!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
106 | |
94 | |
77 | |
65 | |
53 |
User | Count |
---|---|
147 | |
106 | |
104 | |
87 | |
61 |