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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
TaufikMaggangka
Helper II
Helper II

Retrive Status From other table using Date range

Hi Everyone,

Really need help from expert to retrive status from other table using date parameter and ID. Here my case :
Table 1

IdJoining DatePosition
11 January 2021Teacher
11 January 2022Admin


Table 2

IdAttendance Date
12 February 2021
13 March 2021
12 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:

IdAttendance DatePosition
12 February 2021Teacher
13 March 2021Teacher
12 July 2022Admin


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.

2 ACCEPTED SOLUTIONS
tamerj1
Super User
Super User

Hi @TaufikMaggangka 

Are the two tables linked via ID column? Are you looking for a measure or a calculated column?

View solution in original post

@TaufikMaggangka 
Yes you are right, I did it the other way around which was wrong.

Please refer to attached sample file with the solution

1.png

 

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

 

View solution in original post

5 REPLIES 5
TaufikMaggangka
Helper II
Helper II

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.

Hi @TaufikMaggangka 

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

Thank you for trying to help @tamerj1 , the result is not my expetations.

I try this on PBIX file here

If using the sample case on the PBIX file, I hope the result will be like this :

IDAttendance DatePosition
12 February 2021Teacher
13 March 2022Admin
22 February 2021Teacher


Really need your future help.

@TaufikMaggangka 
Yes you are right, I did it the other way around which was wrong.

Please refer to attached sample file with the solution

1.png

 

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

 

tamerj1
Super User
Super User

Hi @TaufikMaggangka 

Are the two tables linked via ID column? Are you looking for a measure or a calculated column?

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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

Top Solution Authors