Date Bridge Table

[Anonymous]

Hi All,

I using visuals on same page from two different data sources. Neither have a primary key and thew date field is non unique. But I need both sets of visuals to change from the same date slicer. I can't have the client using two date controls on the one page.

I have constructed a dCalendar bridge table with a date field. Then I then a one to many relationship from both source tables to the bridge table. But the data returned is wrong. So I don't know if this appoarch will work or if I need to do something else.

I have two different data sources with date fields Source 1; 'POS Data 1'[Event Time] and Source 2; 'GA -Users'[Date]. All the fields are date data type.

dCalendar = CALENDAR(MIN('GA - Users'[Date]),MAX('GA - Users'[Date])) I have also tried using the date from the POS Data 1 sources.

Thanks for any help.

[v-chuncz-msft]

@Anonymous,

Check if the relationship established for table 'POS Data 1' is right, not column [Event Time].

[GilbertQ · Accepted solution]

Hi @Anonymous

Can you try changing the Cross Filter direction to single on both relationships?

The Cross Filter direction set to BOTH can affect the other tables.

Also how did you go about creating your dCalendar table? I would suggest creating an independant table. I do have a blog post explaining how to do this: https://gqbi.wordpress.com/2016/09/13/power-bi-how-to-easily-create-dynamic-date-tabledimension-with-fiscal-attributes-using-power-query/

[Anonymous]

I don't see why yours was any different to mine, but it seems to work. Thanks a lot for the blog very helpful.

[GilbertQ]

Hi @Anonymous

There are often things that are very small between what is different and can be hard at times to see.

[Anonymous]

Hi @GilbertQ

I have noticed that week of the year number is incorrect. The second week of the year starts on second row 2/01/2017 when it should start on the 9/01/2017.

Start year paramater is 2017 and fiscal start month is 7.

Not sure what has cuased this to be incorrect.

Cheers

[Anonymous]

Is what I am doing supposed to work? If I make a different calendar table like yours I still need to make a relationship from the two source tables, correct?