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
)