Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to 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.
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.
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.
when i try that formula it give me an error saying "the syntax for '4'" is incorrect, please advise
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
113 | |
99 | |
75 | |
73 | |
49 |
User | Count |
---|---|
145 | |
109 | |
109 | |
90 | |
64 |