Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
Hi Everyone,
Really need help from expert to retrive status from other table using date parameter and ID. Here my case :
Table 1
Id | Joining Date | Position |
1 | 1 January 2021 | Teacher |
1 | 1 January 2022 | Admin |
Table 2
Id | Attendance Date |
1 | 2 February 2021 |
1 | 3 March 2021 |
1 | 2 July 2022 |
I want to retrive Position from table 1 to table 2, but using joining date(Table 1) and attendance date(Table 2) column as parameter, as you can see Id "1" have 2 position that different in 2021 and 2022.
I hope the result will be like this table:
Id | Attendance Date | Position |
1 | 2 February 2021 | Teacher |
1 | 3 March 2021 | Teacher |
1 | 2 July 2022 | Admin |
As you can see the id on 2 july 2022 being differnet because there were new position from 1 january 2022, and the id took attendance in 2 july 2022.
Really need help from expert, already stuck with this for few days. Already tried with lookup value and firstnon blank function but not working.
Solved! Go to Solution.
Are the two tables linked via ID column? Are you looking for a measure or a calculated column?
@TaufikMaggangka
Yes you are right, I did it the other way around which was wrong.
Please refer to attached sample file with the solution
Position =
VAR T1 = RELATEDTABLE ( 'Table 1' )
VAR T2 =
FILTER (
T1,
VAR CurrentAttendanceDate = 'Table 2'[Attendance Date]
VAR CurrentJoiningDate = 'Table 1'[Joining Date]
VAR T3 = FILTER ( T1, 'Table 1'[Joining Date] > CurrentJoiningDate )
VAR NextJoiningDate = COALESCE ( MAXX ( T3, 'Table 1'[Joining Date] ), TODAY ( ) )
RETURN
CurrentAttendanceDate >= CurrentJoiningDate
&& CurrentAttendanceDate < NextJoiningDate
)
RETURN
MAXX ( T2, 'Table 1'[Position] )
Hi @Tamer yes, the table linked by ID columns and I need calculate column. I need to retrive position from table 1 to table 2. I write down the desire result on my case.
Really hope you can help, Already stuck.
please try
Position =
VAR CurrentDate = Table2[Attendence Date]
VAR CurrentIDTable =
CALCULATETABLE ( Table2, ALLEXCEPT ( Table2, Table2[id] ) )
VAR TableAfter =
FILTER ( CurrentIDTable, Table2[Attendence Date] > CurrentDate )
VAR NextDate =
COALESCE ( MAXX ( CurrentIDTable, Table2[Attendence Date] ), TODAY () )
VAR T1 =
FILTER (
RELATEDTABLE ( Table1 ),
Table1[Joining Date] >= CurrentDate
&& Table1[Joining Date] < NextDate
)
RETURN
MAXX ( T1, Table1[Position] )
@TaufikMaggangka
Yes you are right, I did it the other way around which was wrong.
Please refer to attached sample file with the solution
Position =
VAR T1 = RELATEDTABLE ( 'Table 1' )
VAR T2 =
FILTER (
T1,
VAR CurrentAttendanceDate = 'Table 2'[Attendance Date]
VAR CurrentJoiningDate = 'Table 1'[Joining Date]
VAR T3 = FILTER ( T1, 'Table 1'[Joining Date] > CurrentJoiningDate )
VAR NextJoiningDate = COALESCE ( MAXX ( T3, 'Table 1'[Joining Date] ), TODAY ( ) )
RETURN
CurrentAttendanceDate >= CurrentJoiningDate
&& CurrentAttendanceDate < NextJoiningDate
)
RETURN
MAXX ( T2, 'Table 1'[Position] )
Are the two tables linked via ID column? Are you looking for a measure or a calculated column?
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
25 | |
21 | |
20 | |
14 | |
13 |
User | Count |
---|---|
43 | |
37 | |
25 | |
24 | |
22 |