Forum Discussion

Anonymous

Not applicable

6 years ago

Solved

Dim_Date join with multiple fact date column

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

amitchandak
6 years ago
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

8 Replies

amitchandak
Super User
6 years ago
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
  Not applicable
  6 years ago
  if i dnt join on datetime, how can i extract hourly data
  - Anonymous
    Not applicable
    6 years ago
    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.
- Anonymous
  Not applicable
  6 years ago
  understood now. created calendar and hour table and joined the timestamp with facts
v-kelly-msft
Community Support
6 years ago
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
  Not applicable
  6 years ago
  But i want to show Date table - Hour, Fact1 - Count(custid), fact2(salesamt) in same graph
  - v-kelly-msft
    Community Support
    6 years ago
    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!