Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Score big with last-minute savings on the final tickets to FabCon Vienna. Secure your discount

Reply
Anonymous
Not applicable

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 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?

1 ACCEPTED SOLUTION
Anonymous
Not applicable

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)))

View solution in original post

4 REPLIES 4
Anonymous
Not applicable

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
Super User
Super User

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
Not applicable

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.

@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

Helpful resources

Announcements
August Power BI Update Carousel

Power BI Monthly Update - August 2025

Check out the August 2025 Power BI update to learn about new features.

August 2025 community update carousel

Fabric Community Update - August 2025

Find out what's new and trending in the Fabric community.