Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

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

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...
Need Help
  • Anonymous's avatar
    Anonymous
    7 years ago

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

Cmcmahan's avatar
Cmcmahan
Resident Rockstar
7 years ago

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