Using a Date Table to relate to another table that doesn't contain dates

[OuluChris]

Hi,

I have two tables in Power Query.

The first table contains staff information; Name, Cost Rate, Availability (a decimal number for part-time staff), and expected utilisation. If I multiply these all together I get an average daily cost rate.

I also have a calendar table where I've calculated the number of working days (i.e. excluding weekends and public holidays). 1 is a working day and zero is a non-working day.

I want a measure that allows me to multiply the cost rate by the number of working days. I will then filter this value by month. Unfortunately, because my first table doesn't contain any dates I can't create a relationship and so when I try to get this information I see an error that says "Relationships between tables may be needed"

Is there any way around this?

Many thanks,

Chris

[lbendlin · Accepted solution]

You are looking for a CROSSFILTER, a cartesian product of unrelated tables.

Please provide sanitized sample data that fully covers your issue. Paste the data into a table in your post or use one of the file services. Please show the expected outcome.

[v-stephen-msft · Accepted solution]

Hi @OuluChris ,

I try to reproduce.

Here's the sample data.

calendar table

staff information table

Try using Table.RowCount and Table.SelectRows in Power Query.

= Table.RowCount(Table.SelectRows(calendar, each [working days] =1))

Then you can multiply it easily.

Best Regards,

Stephen Tao

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

[lbendlin · Accepted solution]

You are looking for a CROSSFILTER, a cartesian product of unrelated tables.

Please provide sanitized sample data that fully covers your issue. Paste the data into a table in your post or use one of the file services. Please show the expected outcome.