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.
Hello All,
I am having issues getting two different tables to join a relationship between dates. I and tracking banner ads and I am trying to compare DFA and DFP number as you can see below. My tables work by themselfs but they do not work when I try to combine them together. Please see the below and let me know if someone can help. I can send the data file if needed too.
Solved! Go to Solution.
After you've created your date table; you can go to the relationship builder and link the tables to the date table. You will then be able to use the datekey for example from your date table as the "Date" component as it will be a perfectly setup date list for you.
@asilver1982 Check the formatting of the dates in Power BI, make sure they aren't Text.
Otherwise the better solution is to create a date table. This will ensure that all dates in both tables are accounted for (continous days). There are a number of ways to do this, but building this in DAX is pretty straightforward.
Just set the dates you want in the initial section. * The provided DAX solution below was from another thread here, I just can't find it atm.
Date =
ADDCOLUMNS (
CALENDAR (DATE(2000,1,1), DATE(2025,12,31)),
"DateAsInteger", FORMAT ( [Date], "YYYYMMDD" ),
"Year", YEAR ( [Date] ),
"Monthnumber", FORMAT ( [Date], "MM" ),
"YearMonthnumber", FORMAT ( [Date], "YYYY/MM" ),
"YearMonthShort", FORMAT ( [Date], "YYYY/mmm" ),
"MonthNameShort", FORMAT ( [Date], "mmm" ),
"MonthNameLong", FORMAT ( [Date], "mmmm" ),
"DayOfWeekNumber", WEEKDAY ( [Date] ),
"DayOfWeek", FORMAT ( [Date], "dddd" ),
"DayOfWeekShort", FORMAT ( [Date], "ddd" ),
"Quarter", "Q" & FORMAT ( [Date], "Q" ),
"YearQuarter", FORMAT ( [Date], "YYYY" ) & "/Q" & FORMAT ( [Date], "Q" )
)
@Seth_C_Bauer Thanks for that info. Where do I enter the Dates of each column that I am using?
After you've created your date table; you can go to the relationship builder and link the tables to the date table. You will then be able to use the datekey for example from your date table as the "Date" component as it will be a perfectly setup date list for you.
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 |
---|---|
114 | |
99 | |
82 | |
70 | |
61 |
User | Count |
---|---|
149 | |
114 | |
107 | |
89 | |
67 |