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

v-xicai's avatar
v-xicai
Community Support
7 years ago

Hi Anonymous ,

 

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.