Using an IF statement comparing two date fields in two different tables

[jbrijalba]

I have a master table (will call it Table1) that has a date range, there are also 22 people in the table (meaning a certain date can show up 22 times, while some dates never show up). I have a second table (will call it date table) that is just every single day in that same range. I created a relationship based on the date field, one (date table) to many (table1). I am trying to compare the two tables to see if someone in table1 does not have a value for a date in the date table.

I have tried Matched = If('date table'[Date] = related('table1'[date]), "Y", "N"). I get some message saying that there is no connection.  Now if I switch the two tables around and have table1 first then it works but it does not give me what I need.

I saw something about relatedtable and other quires while searching but nothing. 

Any help will be appreciated.

[jbrijalba · Accepted solution]

Just wanted to let everyone know that I figured it out, not with DAX however. I copied the master table, pasted it to a new Excel file, deleted everything but the date, name and location field. Made the date field to contain all the dates in the range for each person (so 365 days for all 22 people, 365 * 22) . I then in Power Bi created a key column in each table. Key = Concatenate([Name], Concatenate(" - ", [Date]). I created the relationship based on the Key column in each table. From there I was able to get a one to one relationship and use the Related() funtion comparing the Date table to the able with the Peoples work days. 

Thank you everyone for your help. 🙂

[jbrijalba]

Sorry when I said "Made the Date field to contain.....) I meant Date Table**

[jbrijalba]

Thank you all for replying.

The first table (Table1) looks similar to this . With a lot more people and a lot more dates.

The second table (Date Table) looks similar to , again with a lot more dates.

The final table I am aiming to acheive after the DAX expression should look something like . 

Sorry for the long reply but I think the visuals will help.

[jdbuchanan71]

Give this measure a try, it should do what you are looking for.

Working = 
VAR HasDate = COUNTROWS(Table1)
RETURN IF ( ISBLANK(HasDate), "N","Y")

[Amit2019]

Hi,

Try with below DAX expression-

LOOKUPVALUE(table1[date], table1[date],date[date])

It  will give table1[date] (single value) if matches found and blank otherwise.

Hope this will help.

Thanks,
Amit Dhiman

[v-xicai]

Hi @jbrijalba ,

The RELATED function requires that a relationship exists between the current table and the table with related information. You specify the column that contains the data that you want, and the function follows an existing many-to-one relationship to fetch the value from the specified column in the related table. That is to say the one relationship side Field should be into RELATED function ,like RELATED(one relationship Field).

So try to change your formula like DAX below.

Matched = If( 'table1'[date]= related('date table'[Date]), "Y", "N")

Best Regards,

Amy

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Cmcmahan]

Could you use CONTAINS?  Once your data is filtered by person, checking if the [date] column in table1 contains each date in table2 should work.

MATCHED = IF(CONTAINS('table1',[date],SELECTEDVALUE('table2'[date])),"yes","no")