2 Fact Tables, but I can't create relationships from each to a Date Table. Stuck

[Anonymous]

I have a table with POS, and another with Shipments.
I concated the Year and Period on each of these tables into a new column for each, called "Year.Period"

I did the same on my Date table called it "Year.Period"

When I go to connect a relationship from each fact table to the date table, it says I can only have 1 active relationship..

I want to be able to look at both Fact table metrics using the date table for the dates on the same visual.

I'm stuck and would love some help. 

[parry2k]

@Anonymous seems like a year and period column data type is set to text before you add this new column, can you change the data type of these columns to number because that is what is required to add the new date column.

✨ Follow us on LinkedIn and  to our YouTube channel

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make effort to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡ Visit us at https://perytus.com, your one-stop shop for Power BI-related projects/training/consultancy.

[Anonymous]

Thanks again @parry2k. I completed the adding of "date" column to Shipments. 
I then connected "shipment table" to "date table", and "POS Table" to "date table".

I'm still getting the same error though. I am unable to make both relationships active... Any ideas? Thanks again!

Any help is appreciated!!

[parry2k · Accepted solution]

@Anonymous in PQ add a new column with the following expression:

make sure column name are exact, since PQ is case sensitive

#date([Time.Year], [Time.Period], 1 )

using DAX to add new column, use this DAX expression for a new column

My Date Column = DATE ( YourTime[Time.Year], YourTime[Time.Period], 1 )

✨ Follow us on LinkedIn and  to our YouTube channel

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make effort to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡ Visit us at https://perytus.com, your one-stop shop for Power BI-related projects/training/consultancy.

[Anonymous]

Thanks @parry2k ! I tried to add the column you were nice enough to help me out with to the Shipments table.

However, I get this error...

[parry2k]

@Anonymous you can add dummy date using the period column, maybe first of each period month and that will become your date and then you can set the relationship in the date column.

✨ Follow us on LinkedIn and  to our YouTube channel

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make effort to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡ Visit us at https://perytus.com, your one-stop shop for Power BI-related projects/training/consultancy. 

[Anonymous]

Thanks! yes that could definetly work. So I know how to create a column. How would I exactly make it a dummy column I can have a date in? Would you be able to help with the formula please?

These are the only date columns I have in Shipments table....

[parry2k]

@Anonymous why you are having many to many with the date table, you should be able to connect on date column instead of year.period column

✨ Follow us on LinkedIn and  to our YouTube channel

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make effort to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡ Visit us at https://perytus.com, your one-stop shop for Power BI-related projects/training/consultancy.

[Anonymous]

@parry2k Thanks for responding!

My POS table has a "week end date". I created a one to many relationship now, from the Date tabe (date field) to the POS Table (Week end date).

The issue is my Shipments table only goes down to Year and Period (period as in month number). So I don't have a date I can create a relationship to "date" in the Date table with. That's why I created the year.period column on all three tables, and ran into the issue I am running into now.