How to mark Date table?

this link doesn't work

MattAllington, the 'Mark As Date Table' function alters the behavior of filter arguments to CALCULATE() (all of the built in scalar Time Intelligence functions are syntactic sugar for CALCULATE( [measure], <table Time Intelligence function). Normally, when we define a filter argument in CALCULATE() it is rewritten as follows:

// DAX
CALCULATE(
    [BaseMeasure]
    ,DimTable[FilterField] = <literal>
)

// That is rewritten internally to the
// following format

CALCULATE(
    [BaseMeasure]
    ,FILTER(
        ALL( DimTable[FilterField] )
        ,DimTable[FilterField] = <literal>
    )
)

When we 'Mark as Date Table', these semantics are altered slightly. Any filter applied to the marked date table will first clear *ALL* filter context from the table, rather than just the filter field.

// DAX
// If our DimTable is a marked date table, we'll
// see an internal rewrite like this:
CALCULATE(
    [BaseMeasure]
    ,MarkedDateDim[FilterField] = <literal>
)

// This is converted to the following semantics
CALCULATE(
    [BaseMeasure]
    ,FILTER(
        ALL( MarkedDateDim )
        ,MarkedDateDim[FilterField] = <literal>
    )
)

This semantic shift is reflected in the built-in Time Intelligence functions.

The most dramatic difference is that marking a date table will allow you to use a field other than the date to join between fact and DimDate. It's very common, especially in established SQL DWs and Multidimensional OLAP to see the join key between fact and DimDate be defined on an integer field, with the date-type field being simply an attribute of that integer key.

This works fine with a marked date table, but if you don't 'Mark as Date Table', then an integer key will function incorrectly with the built-in time intelligence in DAX. We don't have marking functionality in PBI, so we *must* join on the date field between fact and DimDate for the built-in Time Intelligence functions to work.

Try it for yourself. Pull in a date dimension with an integer key and some fact with both [DateKey] and [Date] in it. Use some Time Intelligence functions. Then set the active relationship to [Date]. Everything's great. Then try it on [DateKey]. It doesn't work correctly.

What causes this behavior for an integer key? When the Time Intelligence function filters [Date], it clears context on [Date]. If we're joined on [DateKey], that context is not cleared. If we've joined on [Date], then there's no conflict in context between the join key and the filter field.