Forum Discussion

TaufikMaggangka's avatar
TaufikMaggangka
Helper II
3 years ago
Solved

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 Id Joining Date Position 1 1 January 2021 Teacher 1 ...
DAX
retrive data
  • tamerj1's avatar
    tamerj1
    3 years ago

    Hi TaufikMaggangka 

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

  • tamerj1's avatar
    tamerj1
    3 years ago

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

     

tamerj1's avatar
tamerj1
Community Champion
3 years ago

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

TaufikMaggangka's avatar
TaufikMaggangka
Helper II
3 years ago

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.

  • tamerj1's avatar
    tamerj1
    Community Champion
    3 years ago

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