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

Join us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.

Reply
Anonymous
Not applicable

Filter date column if that date exists in another table -result has to be a column- For LookupValue

Hi guys, 
I need to create a column (ColumnQuestion), that if the date on Table2 is the same Date as Table1 then use Table2 values(this is the first LOOKUPVALUE in the script.)

 

The lookupvalues work great. I need help creating the filter that compares both dates, IF these dates are the same, then bring the "NumberofDays" from Table2 into Table1. (Please see that the lookupvalue has to be done by itemid and date.)

Table 2 only has 1 date. 

Table1 has previous dates and future dates.
If I need to use MIN or MAX function I don't know how to use it in this case, with the double lookupvalue and the filter.

 

 

ColumnQuestion =
IF (
    FILTER ( 'Table1', 'Table2'[DateValue] = 'Table1'[DateValue] ),
    LOOKUPVALUE (
        'Table2'[NumberofDays],
        'Table2'[ItemID], 'Table1'[ItemID],
        'Table2'[DateValue], 'Table1'[DateValue]
    ),
    IF (
        'Table1'[Date Past or Future] = "Past",
        'Table1'[HistoricNumberofDays],
        LOOKUPVALUE (
            Table3[NumberofDays],
            Table3[ItemID], 'Table1'[ItemID],
            Table3[Year], 'Table1'[Year],
            Table3[Week], 'Table1'[Week]
        )
    )
)

 

Thank youuuu

2 REPLIES 2
Anonymous
Not applicable

Thank you! I ended up creating a new column for current date, and that worked! 

amitchandak
Super User
Super User

@Anonymous , You can get value from another table in a new column like. Change logic as per need

 

new colum =
var _Tab2= maxx(filter('Table2', 'Table2'[ItemID] = 'Table1'[ItemID] && 'Table2'[DateValue] = 'Table1'[DateValue]), 'Table2'[NumberofDays])
var _Tab3=
maxx(filter('Table3', 'Table3'[ItemID] = 'Table1'[ItemID] && 'Table3'[year] = 'Table1'[Year] && 'Table3'[Week] = 'Table1'[Week ] ), 'Table2'[NumberofDays])
return
max(_Tab2,_Tab3)

 

refer 4 ways (related, relatedtable, lookupvalue, sumx/minx/maxx with filter) to copy data from one table to another
https://www.youtube.com/watch?v=Wu1mWxR23jU
https://www.youtube.com/watch?v=czNHt7UXIe8

Full Power BI Video 20 Hours YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube

Helpful resources

Announcements
Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.

June 2025 Power BI Update Carousel

Power BI Monthly Update - June 2025

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

June 2025 community update carousel

Fabric Community Update - June 2025

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