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

     

TaufikMaggangka's avatar
TaufikMaggangka
Helper II
3 years ago

Hi Anonymous 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.

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