Matching 2 columns from 2 different tables to return a record from adjacent column

[Anonymous]

Hi everyone, I'm kinda new to DAX formulas and I'm struggling with this particular formula.

So i've used this formula to calculate the the most recent date of all the records that correspond to the particular identifier (Level 1).

CALCULATE(LASTDATE(Oil[Date]),FILTER(ALL(Oil),Oil[Level 1]='Level 1'[Level 1]))

So now I want to match the date calculated from the formula above (which is in the table 'Level 1'), to the date in the Oil table to get the corresponding record from the Index column, which is in the 'Oil' table.

If any more clarification is needed, please do shout.

Much appreciated

Neena 😃

[Anonymous]

Hi @Anonymous 
Look at DAX lookupvalue function.

Cheers!

[Anonymous]

 Hi @Anonymous 

I've tried the lookupvalue function.

This is the formula I came up with:

LOOKUPVALUE(Oil[Report Index],Oil[Date],'Level 1'[Latest Date])

However, I keep getting the error that I'ne supplied the formula with multiple tables, while it was expecting a single value, which I get. I'm just having trouble writing a formula that will work.

Thoughts?

Cheers 😃

Neena

[Anonymous]

Hi @Anonymous 

Your formula looks correct.

Please share your data for a more accurate solution.

Cheers!

[Anonymous]

Hi@Anonymous 

So this is a small snapshot of what my 'Level 1' table looks like:

Level 1       Latest Date  

B                                     null

C                                     null

D                                     null

H                  25/01/2019

J                    25/01/2019

L                    25/01/2019

FYI... Level 1 is the PK and there are more records

The Latest Date column was calculated using this formula

CALCULATE(LASTDATE(Oil[Date]),FILTER(ALL(Oil),Oil[Level 1]='Level 1'[Level 1]))

This is a small snapshot of what my Oil table looks like:

Level 1                    Report                                       Date                   Report Index

H                                   Good                       01/01/2014                                              1

J                             Trending                       02/09/2016                                              2

J                                     Good                       25/01/2019                                              1

H                                   Good                        25/01/2019                                              1                                             

L                                    Good                        25/01/2019                                               1                                        

FYI... There are more records

So what I'm tryinng to do is mtach the Latest Date column in thge Level 1 table and the Date column in trhe Oil table to give me the value in the corresponding Report Index column.

Classic VLOOKUP I know, but it's just not working for me =(

Cheers,

Neena

[Anonymous]

Hi @Anonymous 

The problem is that you have 1:* relationship.

Which value would you like to return for H for example? 25/01/2019  OR 01/01/2014.

Which value would you like to return for J for example? 02/09/2016 OR 25/01/2019. Index 1/2

etc...

Can you answer? I can NOT answer, as there are multiple options for an answer.

In this case you will have to narow down the options.

Your data model does not support what you are looking for at this point.

Thanks
A

[Anonymous]

Ah right, I understand

Thanks