Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
alisonpappas
Helper III
Helper III

relationships between calendar table and imported data

Hi,

 

I am trying to make a relationship betweenan excel file I imported and a calendar table I created in Power BI. I have a bunch of data I want to be able to analyze on a rolling period basis. When I attempt to connect the Data table to the Date table it says I am unable to because in order to do Many to One, we need unique values. When I create the Bridge table it is still unable to connect the values together. I am also not able to append or merge any queires because the calendar table was created directly in Power BI and the data was imported from Excel. My actual data is only able to be broken down into YearQuarer (ex: 2015Q3) and not the actual date.

 

I am looking for help to connect the dates and data together. Unsure if the bridge can be fixed or if there is another way to combine.

1 ACCEPTED SOLUTION

Hi,

Try this calculated column formula

=IF(Data[Quarter]="Q1",DATE(Data[Year],1,1),IF(Data[Quarter]="Q2",DATE(Data[Year],4,1),IF(Data[Quarter]="Q3",DATE(Data[Year],7,1),DATE(Data[Year],10,1))))

Hope this helps.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

7 REPLIES 7
darentengdrake
Resolver II
Resolver II

Hi @alisonpappas 

 

Does the calendar table have unique values? Can you show how you create your calendar table and a sample data?

Hi @darentengdrake ,

The top photo is the calendar I made and the bottom is my data. I want to be able to connect them by "Period" the running monthes-month index were all created by dax formula equations. 

Capture.PNGCaptur 2e.PNG

Hi,

From the period column, we will have to first construct a Date.  Assuming the quarters are calendar quarters, try this calculated column formula to create a Date column

= IF(RIGHT(Data[Period],2)="Q1",DATE(LEFT(Data[Period],4),1,1),IF(RIGHT(Data[Period],2)="Q2",DATE(LEFT(Data[Period],4),4,1),IF(RIGHT(Data[Period],2)="Q3",DATE(LEFT(Data[Period],4),7,1),DATE(LEFT(Data[Period],4),10,1))))

Now create a relationship from the this column to the Date column in the Calendar Table.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Hi @Ashish_Mathur 

 

when i try that formula it give me an error saying "the syntax for '4'" is incorrect, please advise

Hi @alisonpappas 

 

Can you show me your bridge table as well?

hi @darentengdrake

 

this is the bridge i have as of right now 

 Capture.PNG

Hi,

Try this calculated column formula

=IF(Data[Quarter]="Q1",DATE(Data[Year],1,1),IF(Data[Quarter]="Q2",DATE(Data[Year],4,1),IF(Data[Quarter]="Q3",DATE(Data[Year],7,1),DATE(Data[Year],10,1))))

Hope this helps.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.