Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Lookup Value in multiple tables based on a key

I have an extremely large calculated table that is consolidated from unique keys across 3 different tables. Each of the tables has a date column that corresponds to a key and I would like to show tha...
Need Help
Tutorial Requests
  • Anonymous's avatar
    Anonymous
    5 years ago

    I was able to solve it by doing the following 

    Date Column = 
var lookup1 = CALCULATE(FIRSTNONBLANK('Table 1' [Date], 1), FILTER( ALL ('Table 1'), 'Table 1'[Key] = 'Calc Table'[Key])) 

var lookup2 = CALCULATE(FIRSTNONBLANK('Table 2' [Date], 1), FILTER( ALL ('Table 2'), 'Table 2'[Key] = 'Calc Table'[Key])) 

var lookup3 = CALCULATE(FIRSTNONBLANK('Table 3' [Date], 1), FILTER( ALL ('Table 3'), 'Table 3'[Key] = 'Calc Table'[Key])) 

return IF(NOT(ISBLANK(lookup1)), lookup1,
IF(NOT(ISBLANK(lookup2)), lookup2,
IF(NOT(ISBLANK(lookup3)), lookup3)))
selimovd's avatar
selimovd
Icon for Most Valuable Professional rankMost Valuable Professional
5 years ago

Hey Anonymous ,

 

with the information you gave:

I would try with CALCULATE and FILTER or with LOOKUPVALUE or with INTERSECT.

 

If you need any help please let me know.
If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍
 
Best regards
Denis
 
Anonymous's avatar
Anonymous
Not applicable
5 years ago

Sorry, selimovd, I am new to DAX so I'm not sure how I would write this out. I can do CALCULATE(FIRSTNONBLANK('Table 1' [Date], 1), FILTER( ALL ('Table 1'), 'Table 1'[Key] = 'Calc Table'[Key])) but this will only return the date for key values that match Table 1. I don't know how to search the other two tables.