How to lookupvalue in second table where text value picks earlier date that is not null

[userdata]

Table 1                                                                          Table 2

date |  mean                                                                  date | std

27.4     34                                                                        24.4    c

12.3     12                                                                        12.3   blank

                                                                                         10.3 x

                                                                                           9.3  b

output i want to see:

date |  mean  | std                                                                

27.4     34        c                                                               

12.3     12        x

there are two different table 1 and 2 and what i need to do is lookup if date in first table is 12.3 then i need to check in table 2 if there is a std for 12.3 if not i have to take the latest value that is not blank, in this case it would be x.

How would I do that in DAX

[v-yadongf-msft · Accepted solution]

Hi @userdata ,

I created two tables the same as you.

Table1:

Table2:

Please create a new column and try following DAX:

std = 
VAR lookup = LOOKUPVALUE('table2'[std],'table2'[date],'table1'[date])
RETURN
IF(
    NOT ISBLANK(lookup),lookup,
    MAXX(
        FILTER('table2','table2'[date]),
        'table2'[std])
        )

Then you can get result you want.

^{Best Regards,}

^{Yadong Fang}

^{If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.}

[userdata]

@v-yadongf-msft Thanks so much for this!! You are a herooo!

[userdata]

@amitchandak  thanks for this . I tried to use the calculated column but I get the same value for std but it should change based on date.  The values do not show correctly

[userdata]

Anyone else who can help on this? How do I create a lookup for the latest date that is not null from table 2? Thanks!

[amitchandak]

@userdata , New column in table one

col  =

var _min = minx(filter(Table2, table2[Date] <= table1[Date]) , Table2[Date])

return

minx(filter(Table2, table2[Date] =_min) , Table2[std])