Forum Discussion

Quafe's avatar
Quafe
Regular Visitor
9 years ago
Solved

Date dimension and multiple date columns in fact table

Straight to the point: how does one deal with multiple columns containing date values in the master fact table, and a separate date dimension? The way our data warehouse tables are structured, a s...
Need Help
  • Phil_Seamark's avatar
    Phil_Seamark
    9 years ago

    You can have multiple relationships between your Fact table and your Date Dimension table.

     

    So a FACT table with [Order Date] , [Ship Date] and [Delivery Date] can all be related to your Date dimension.

     

    Only one relationship can be Active!  You should set this to be the most common link.

     

    Then you create measures per date action and use the DAX function called USERELATIONSHIP 

     

    https://msdn.microsoft.com/en-us/library/hh230952.aspx

     

    So a measure counting the number of deliveries will specify that the Inactive relationship is to be used for that particular calculation.

     

    A bit of a pain but it's not as bad as it sounds.

     

    Otherwise use an MDX cube and you will have no such issues :)

Anonymous's avatar
Anonymous
Not applicable
6 years ago

You want to user CROSSFILTER next to USERELATIONSHIP. It will ignore date filtering that comes from an active relationship and use the one that is set with USERELATIONSHIP. So 5 measures with different dates in one table, will use different dates to calculate, when single filter is being used. 

 

CROSSFILTER and USERELATIONSHIP is not needed for the measures that use active relationship.

CALCULATE (
    CALCULATE (
        SUM ( 'Table'[Column 1] ),
        USERELATIONSHIP ( 'Table'[Date 1], 'Master Calendar'[Date] )
    ),
    CROSSFILTER ( 'Table'[Date 2], 'Master Calendar'[Date], NONE )
)

 

  • a1b1c1's avatar
    a1b1c1
    Icon for Advocate I rankAdvocate I
    6 years ago

    Anonymous Thanks for getting back to me instantly. 
    It's the case that I want to display two date columns ('Attended On' and 'Registered On') in a simple table visual. I don't want to create any calculations like sum of amount etc based on those dates. As you can see in the screenshot attached, there are some blank values in both the columns. 

     

    I have an active relationship of 'Attended on' with Date Dimension and an inactive relationship with 'Registered On'. 
    I was using this measure to activate the 'Registered On' relationship: 
     

    Registered On =
    CALCULATE(
    IF( MAX('Fact Table (2)'[Registered On])<>BLANK(), MAX('Fact Table (2)'[Registered On]), BLANK() ),
    USERELATIONSHIP ( 'Fact Table (2)'[Registered On], 'Date Dimension'[Date]))

     

    It poses two problems, one of which you had mentioned in your previous reply. It ignores the active relationship filtering. Second, it doesn't display values where the date value is blank. Somehow, the dax relationship only returns values where the resulting date value is not blank. 

    I tried using your measure, it wasn't working somehow. Can you please share any ideas as to how to get this working?