Are joins on date data type left outer joins?

[imnikki]

I have a custom date dimension table and it is used to calculate a cumulative headcount from my FactHeadcount table. When I join columns of data type “date”, I get the results I expect.  When I join columns on a key “DateKey” of type integer, the Headcount measure breaks.

Other details:

• DimDateFY table has one record for each day between Jan 1, 2015 and Dec 31, 2017
• FactHeadcount table “Effective date” start from 1960 and go to Dec 2016
• I have a report filter selecting the months of Mar 2016 to Feb 2017 from the DimDateFY table
• Headcount =                
      CALCULATE (                                
          SUM (FactHeadcount[Counter]),                                
              FILTER (                                                
                  ALL (DimDateFY[Date]),                                                
                  (DimDateFY[Date]) <= MAX (DimDateFY[Date])                                
              )                
          )
• The only change between behaviors is an update to the relationships

If Power BI joins are inner joins, why is it that it becomes a left outer join if the join is on a data type “date”? Is this expected behavior?  Will it stay this way? 

Is it possible to modify the measure to obtain the correct headcount, with the report filter, and using the “integer” inner join?

If you have advice and/or documentation on this, it would be greatly appreciated.

[imnikki · Accepted solution]

Thank you @greggyb for your explanation in the "How to mark date table" post.  I understand now that the Excel Power Pivot functionality is not available in Power BI yet and it explains the behavior I experienced.

[Anonymous]

Hi @imnikki,

What is the result when you use the following formula to create Headcount measure? Does it change when you use different columns to create relationship between tables?

Headcount =                
    CALCULATE (                                
        SUM (FactHeadcount[Counter]),                                
            FILTER (                                                
                ALL (DimDateFY[DateKey]),                                                
                (DimDateFY[DateKey]) <= MAX (DimDateFY[DateKey])                                
            )                
        )

Thanks,
Lydia Zhang

[imnikki]

Hi Lydia @Anonymous,

I realize that it is best practice to have joins on integer data type and not date. However, the DAX command “ALL” in the headcount formula doesn’t count all records from the table but rather those returned by the “Report level filters”.  See second chart. 

If the join between the fact table and the dimension table is over a “date” data type, the “ALL” command works as desired. See first chart.

Because I want to use an integer key, I decided to create a cumulative total in SQL Server instead and use the “OVER” clause (SQL Server 2012 or newer). I won’t need DAX for the headcount measure.

BTW, I tried to e-mail you my file but I was unsuccessful. The mailbox is full.

Nicole