How to get a correlation diagram between measurements in two tables

[Anonymous]

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!

[v-jingzhang · Accepted 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.