Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
Hi All,
I'm a newby on programming in power query, that's why I need your help.
I've got two fact tables, both with different measurements. I want to get a correlation diagram between 2 measurements, 1 from the first table, the second one from the other table.
Both tables have a product_id and a date column.
I've tried to setup a relationship by product_id, that worked, but the correlationdiagram looks like this:
This is when one of the measurements is set to "average". When both maesurements are set to individual data, then I get an error that Power Bi can't find the link between the two tables. Probably because with one product there are several rows in (each) table.
I wanted to get a link between the dates in the two tables in order to get the correlation diagram in a correct way. But the dates differ between the two tables. Sometimes the dates are the same, sometimes there is one day different, sometimes more (until a max of 4).
I don't want to manually put in the raw data an extra date column the correct dates (too many records).
Is there a way to program this in M?
Thanks for your help!
Solved! Go to Solution.
Hi @Anonymous
You could add a Date Dimension table to your model with M codes or DAX function CALENDAR(). The Date table need to have continuous unique dates in a column. Then create relationships between fact tables and the Date table based on date columns. Use the column from the Date table as X-axis field in the chart. Please refer to this blog: Do You Need a Date Dimension? - RADACAD
To create a simple Date table in M, you could use below codes:
let
StartDate=#date(2012,1,1),
EndDate=#date(2013,12,31),
NumberOfDates = Duration.Days(EndDate-StartDate)+1,
//Generate a continuous list of dates from the start date to the end date
DateList = List.Dates(StartDate, NumberOfDates, #duration(1, 0, 0, 0)),
//Turn this list into a table
TableFromList = Table.FromList(DateList, Splitter.SplitByNothing(), {"Date"} , null, ExtraValues.Error),
//Caste the single column in the table to type date
ChangedType = Table.TransformColumnTypes(TableFromList,{{"Date", type date}}),
//Add custom columns for day of month, month number, year
DayOfMonth = Table.AddColumn(ChangedType, "DayOfMonth", each Date.Day([Date])),
MonthNumber = Table.AddColumn(DayOfMonth, "MonthNumberOfYear", each Date.Month([Date])),
Year = Table.AddColumn(MonthNumber, "Year", each Date.Year([Date])),
DayOfWeekNumber = Table.AddColumn(Year, "DayOfWeekNumber", each Date.DayOfWeek([Date])+1)
in
DayOfWeekNumber
Another option is to use DAX functions to create a Date table. You could refer to this article: Power Bi for Beginners: How to create a Date Table in Power Bi - Softcrylic
Regards,
Community Support Team _ Jing
If this post helps, please Accept it as the solution to help other members find it.
Hi @Anonymous
You could add a Date Dimension table to your model with M codes or DAX function CALENDAR(). The Date table need to have continuous unique dates in a column. Then create relationships between fact tables and the Date table based on date columns. Use the column from the Date table as X-axis field in the chart. Please refer to this blog: Do You Need a Date Dimension? - RADACAD
To create a simple Date table in M, you could use below codes:
let
StartDate=#date(2012,1,1),
EndDate=#date(2013,12,31),
NumberOfDates = Duration.Days(EndDate-StartDate)+1,
//Generate a continuous list of dates from the start date to the end date
DateList = List.Dates(StartDate, NumberOfDates, #duration(1, 0, 0, 0)),
//Turn this list into a table
TableFromList = Table.FromList(DateList, Splitter.SplitByNothing(), {"Date"} , null, ExtraValues.Error),
//Caste the single column in the table to type date
ChangedType = Table.TransformColumnTypes(TableFromList,{{"Date", type date}}),
//Add custom columns for day of month, month number, year
DayOfMonth = Table.AddColumn(ChangedType, "DayOfMonth", each Date.Day([Date])),
MonthNumber = Table.AddColumn(DayOfMonth, "MonthNumberOfYear", each Date.Month([Date])),
Year = Table.AddColumn(MonthNumber, "Year", each Date.Year([Date])),
DayOfWeekNumber = Table.AddColumn(Year, "DayOfWeekNumber", each Date.DayOfWeek([Date])+1)
in
DayOfWeekNumber
Another option is to use DAX functions to create a Date table. You could refer to this article: Power Bi for Beginners: How to create a Date Table in Power Bi - Softcrylic
Regards,
Community Support Team _ Jing
If this post helps, please Accept it as the solution to help other members find it.