Creating a relationship between tables

[Fiona83]

Please can someone kindly help me?

I am trying to create a relationship between two tables, and have tried to use dates as the connection. 

(Table 1) Company A - Staff type - Date - number of staff needed

(Table 2) Company A - staff type - Date - number of staff available

Table 1 has various other data, but I want to show Needs vs Availability 

I am getting the error  "cant create a relationship between these two columns because one of the columns must have unique values."

Staff needed and staff available will have values the same on some cells- is this the issue?

[kamalmsharma]

You may use this dax expression to create a date table quickly. 

Dates_table = CALENDAR(DATE(2018,1,1), DATE(2020,12,31))

Then create relationship between  the two tables with date_table.

You date column of date_table to create visuals.

Hope this helops.

Kamal

www.addendanalytics.com

[kamalmsharma]

You can also use AUTOCALENDAR function to let power bi decide date range for you. It picks up the first date and the last date in your data model for date range 🙂

[AlB · Accepted solution]

Hi @Fiona83 

As the error message says one of the sides has to have unique values to create a direct relationship. You can best create a calendar table and, if needed, create relationships between it and your two current tables. The calendar table will have unique values.  You could also join both tables so that you have both staff needed and available in one place.

Please mark the question solved when done and consider giving kudos if posts are helpful.

Cheers