Lookup Value in multiple tables based on a key

[Anonymous]

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 that date in a new calculated column. How would I write a DAX formula to look up the date in one table, and if it's not there check the next?

[Anonymous · Accepted solution]

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]

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

Blog: WhatTheFact.bi

Follow me: twitter.com/DenSelimovic

[Anonymous]

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.

[selimovd]

@Anonymous that approach might probably work. As I said I don't know your data model, so I cannot tell you what to do. If you want help you have to provide more information.

Please read the following posts:

How to provide sample data in the Power BI Forum - Microsoft Power BI Community

https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/2001860

Best regards

Denis