cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
lukaspowerbi Regular Visitor
Regular Visitor

Linking calendar table and my own table by Month and Year column

I want to create a relationship between two tables, MonthTable and FY18 Table. 

I created MonthTable below with a Year-Month column that has unique values:


Capture.JPG

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Now, I would like to create a relationship with FY18 table which is a little bit tricky. The FY18 table is brought into power bi in multiple .xlsx files, each file represents one month and I created a conditional column based on its source name.

I basically need to create a column based on source name with the same format that I have in my MonthTable.

I already created a conditional Year Month Column but it's not the right format. Can anyone help? Thank you.

Capture.JPG

 

 

 

 

 

 

9 REPLIES 9
Super User
Super User

Re: Linking calendar table and my own table by Month and Year column

You probably need to specify that your Year Month column is Text in your second table. 


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!


lukaspowerbi Regular Visitor
Regular Visitor

Re: Linking calendar table and my own table by Month and Year column

It doesn't seem to work. It allowed me to create 1 to many relationship but tables don't communicate at all. 

Setting up both columns allowed me to create it but it won't work in my slicer.

Super User
Super User

Re: Linking calendar table and my own table by Month and Year column

Create a new column with the formula:

New Year Month = YEAR([Year Month]) & " " & MONTH([Year Month])

Use that to link your tables.

 


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!


Highlighted
lukaspowerbi Regular Visitor
Regular Visitor

Re: Linking calendar table and my own table by Month and Year column

I created the column but it didn't help. I think the issue is wth my FY18 table not the calendar. 

 

Super User
Super User

Re: Linking calendar table and my own table by Month and Year column

Can you supply source data that can be copied and pasted or a PBIX file? This should be pretty straight-forward.


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!


lukaspowerbi Regular Visitor
Regular Visitor

Re: Linking calendar table and my own table by Month and Year column

I think it's my FY18 and creating that conditional column based on Source.Name column.

 

Here is the file:

https://www.dropbox.com/s/82fvcvby74gc3n3/Trend%20Test.pbix?dl=0

 

I appriciate your help!

Community Support Team
Community Support Team

Re: Linking calendar table and my own table by Month and Year column

Hi @lukaspowerbi

 

You may try to link the two table between the New Year Month column and Year-Month column. Here is the document for you.

 

https://docs.microsoft.com/en-us/power-bi/desktop-create-and-manage-relationships

 

Regards,

Cherie

Community Support Team _ Cherie Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
lukaspowerbi Regular Visitor
Regular Visitor

Re: Linking calendar table and my own table by Month and Year column

Thanks, that's what I tried but for some reason doesn't work. I have One to Many Relatonship

My MonthTable with unique values in a Year-Month column:
Capture.JPG

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

My FY18 Table with Year Month column

Capture.JPG

 

 

 

 

 

 

 

 

 

 

 

 

 

Both columns are text, I tried Data type too but didn't work. 

 

 

 

 

Super User
Super User

Re: Linking calendar table and my own table by Month and Year column

When you say it doesn't work, can you be more specific? It doesn't allow you to create the relationship? You aren't getting the correct values in some visual?


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!