DAX calculatetable not filtering on related date table

[brianlarson]

In my model, I have a table called Date with a relationship with a table called IntegrityExceptions.  Date.FullYear has a realtionship with IntegrityExceptions.Exception Date In.  I have created a table like this:

Table 3 =

var yr = MAX('Date'[Year])

RETURN

CALCULATETABLE(IntegrityExceptions,'Date'[Year] = 2021,'Date'[Quarter] = 2)

As I have it above, it returns results but if I change it be:

Table 3 =

var yr = MAX('Date'[Year])

RETURN

CALCULATETABLE(IntegrityExceptions,'Date'[Year] = yr, 'Date'[Quarter] = 2)

It doesn't return any results even though MAX('Date'[Year]) is equal to 2021 based on my date slicer on the canvas.  I can't figure out why it doesn't work. 

[brianlarson]

Thanks Paul.  What would be a way to have a filtered table based on what is selected in the slicers?  I need to be able to create a data set that time travels, one quarter ago, two quarters ago, etc. 

[PaulDBrown · Accepted solution]

You can wrap the CALCULATETABLE in a COUNTROWS function and use the resulting measure as a filter for the visual in the filter pane setting the value to greater or equal to 1.

Something along the lines of:

Table 3 =
VAR yr =
    MAX ( 'Date'[Year] )
RETURN
    COUNTROWS (
        CALCULATETABLE ( IntegrityExceptions, 'Date'[Year] = yr, 'Date'[Quarter] = 2 )
    )

However, if you need to calculate values for other periods based on date slicers, it is highly recommended you create a date table with continuous dates covering the range of dates in the model. You can then use Time Intelligence functions for the calculations

[PaulDBrown]

CALCULATETABLE is a function which creates a table. Calculated (physical) tables are loaded when the model is loaded and are oblivious to slicers (you can check by hard-coding the date in the CALCULATETABLE function to see how it loads)