Dim_Date join with multiple fact date column

[Anonymous]

Hi All,

I have 3 tables 

Dim_date (datetime)

Fact1 (datetime)

Facts 2(datetime)

RealtionShip

Fact1 (datetime) 1:M Dim_date (datetime) Crossfilter direction (BOTH) and Active/ Fact2 (datetime) 1:M Dim_date (datetime) Crossfilter direction (BOTH) and realtionship Active , result i get in pic 1 if i make relationship inactive then i get the result in pic 2

How to resolve this please help

[v-kelly-msft]

Hi @Anonymous ，

You can use "userelationship" function when you make one of the relationship inactive.

Here is the reference.

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

[Anonymous]

But i want to show Date table - Hour, Fact1 - Count(custid), fact2(salesamt) in same graph

[v-kelly-msft]

Hi @Anonymous ，

Can you show me your .pbix file if it's not confidential?You can upload it to onedrive then share the link  to me.

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

[Anonymous]

@v-kelly-msft  Thanks much in advance ,

https://1drv.ms/u/s!ApPgrUdbr1NBbT4-UdfcQufC8Iw?e=SWehbd

if i select any col and walkin from line.my sales,transaction should split based on hour,day so on

as of now day is coming correct sales=140 and walkin=40 and transaction=52,but line should not be extended, hour wise how to do i dnt have nay ideal. please please help

[amitchandak · Accepted solution]

Not clear. But make sure that both table are joined on Date column with date table having no time. Also, all dates are cover in date dimension

[Anonymous]

understood now. created calendar and hour table and joined the timestamp with facts

[Anonymous]

if i dnt join on datetime, how can i extract hourly data

[Anonymous]

Best practices want the Dim_Date to be a list of distinct days with no gaps, with no time.

In this way you will be able to use it as a calendar table for your model and to use all the Time Intelligence functions.

In your fact you should calculate the date and link it the Dim_Date with a 1:M relationship that goes from Dim_Date to Fact.