Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Get value from another table if there is a difference between two dates

Hello Folks, I have 2 given tables. Now I want to create a new column in table as New_Appliance where if there is a date difference between two dates in table 1 then look for value from table 2 wh...
Need Help
Show and Tell
Tips and Tricks
Tutorial Requests
  • Fowmy's avatar
    Fowmy
    5 years ago

    Anonymous 

    Please add the following column to your Table 1. Your results are not in line with the explanation that you provided.  ID 2, but the lowest should be "Mobile" as the lowest date is 16/2/2020.

    New Appliance = 
var __id = 'Table-1'[Id] return
IF( 
    'Table-1'[Inspection Date]  = 'Table-1'[Re-inspection Date] ,
    'Table-1'[Appliance],
    var __mindate  = 
        MINX(
            FILTER(
                'Table-2',
                'Table-2'[Id] = __id
            ),
            'Table-2'[Re-inspection Date]
        ) 
    var __device = 
        MAXX(
            FILTER(
                'Table-2',
                'Table-2'[Id] = __id && 'Table-2'[Re-inspection Date] = __mindate
            ),
            'Table-2'[Appliance]
        )
    return
        __device
)

     

     




Ashish_Mathur's avatar
Ashish_Mathur
Super User
5 years ago

For ID3, why should the answer be Mobile?

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Because initally when the inspection was performed, mobile was used

    • Ashish_Mathur's avatar
      Ashish_Mathur
      Super User
      5 years ago

      Hi,

      So if there is difference in the 2 dates of any ID in Table1, then you want to search for the Inspection date appearing in Table1 in the Inspection Date of Table2 and then bring over the Device from Table2 to Table1.  is my understanding correct?

      • Anonymous's avatar
        Anonymous
        Not applicable
        5 years ago

        Yes, That's Right