DAX Metrics and Date Table configuration

[CCDLA]

Hi everyone!

I've been stuck for a couple of weeks with a metric that at first is quite simple, so I present the case to see if someone can help me.

PreviousExtract = CALCULATE(MAX(Dim_Calendar[Date]),ALLEXCEPT(Dim_Calendar,Dim_Calendar[BillingDay]),Dim_Calendar[Date]<MAX(Dim_Calendar[Date]))

What the metric should return is the previous day keeping the filter of the BillingDate column, where BillingDate is nothing more than a column that returns 1 if it is the 15th of the month and null in any other case. If we filter by BillingDate=1 it should give the 15th of the previous month, however the metric returned the previous day without keeping the filter, that is, the 14th of the same month.

After thinking about it a lot I realized that it was because I had the table configured as a date table, if I eliminate the configuration the metric works perfectly, so the following questions arise:

1.  What is the best way to generate relationships with the date table?
   1.  Relating through an IDDate that is an integer (I was relating it to the corresponding 5-digit integer) and configuring the table as a date table so as not to lose the date logic provided by pbi
   2.  Relating by the Date field directly
2. How should the metric be so that it works as expected, having the table configured as a date table?

Hope someone can help me
Thank you very much in advance!

[CCDLA]

Hi @Greg_Deckler!

It works, but only for The [BillingDay] = 1, as you saidtThe [BillingDay] = 1 should preserve only the rows in the table where the [BillingDay] = 1, but it is not what I want. I want it only when [BillingDay] = 1 is selected in the external filter.

[CCDLA]

Hi @Greg_Deckler!

Thank you very much for the suggestion, I used ALL(Dim_Calendar) for the FILTER() instead of just Dim_Calendar since otherwise the formula would only work for the total and not for each individual date in the table. However, the result is not what I am looking for either, since what I want is to be able to filter on demand Dim_Calendar[BillingDay] = 1 or not, that is why I used ALLEXCEPT() in the original formula.

Do you know what is the reason that makes the formula work by default, but not with Dim_Calendar table marked as date table? I think it has to do with context filters, but I can't find the correct formula...

Thank you!

[Greg_Deckler · Accepted solution]

@CCDLA Right, then just use ALLSELECTED like:

PreviousExtract = 
  VAR __Date = MAX(Dim_Calendar[Date])
  VAR __Table = FILTER(ALLSELECTED(Dim_Calendar), [Date] < __Date)
RETURN
  MAXX(__Table,[Date])

or:

PreviousExtract = 
  VAR __Date = MAX(Dim_Calendar[Date])
  VAR __BillingDays = SELECTCOLUMNS('Dim_Calendar',"__BillingDay",[BillingDay])
  VAR __Table = FILTER(ALL(Dim_Calendar), [BillingDay] IN __BillingDays && [Date] < __Date)
RETURN
  MAXX(__Table,[Date])

[CCDLA]

Hi @Greg_Deckler !

The first one works perfect! The second one lookup the previous date with the same [BillingDay].

Just one last question, could you tell me why this measure does not work with the table marked as a table of dates and yours does, if they are almost the same? What am I missing?

PreviousExtract = CALCULATE (
    MAX ( Dim_Calendar[Date] ),
    ALLSELECTED ( Dim_Calendar ),
    Dim_Calendar[Date] < MAX ( Dim_Calendar[Date] )
)

Thank you very much for your help!

Best regards

[Greg_Deckler]

@CCDLA Well, it's CALCULATE so to some degree, who knows. I wrote a blog article about it. CALCUHATE - Why I Don't Use DAX's CALCULATE Functi... - Microsoft Power BI Community

CALCULATE is an extremely complicated function that is in many ways a black box that people think does magical things. The harsh reality is that if you haven't read and understand everything below, you shouldn't be using CALCULATE and CALCULATE does weird things from time to time that I can't even explain. That's a new one though that just marking it as a date table causes it not to work. My guess is that it's a context thing and that this should work:

PreviousExtract = 
VAR __Date = MAX ( Dim_Calendar[Date] )
RETURN
CALCULATE (
    MAX ( Dim_Calendar[Date] ),
    ALLSELECTED ( Dim_Calendar ),
    Dim_Calendar[Date] < __Date )
)

dax.guide:

A filter argument overrides the existing corresponding filters over the same column(s), unless it is embedded within KEEPFILTERS.

CALCULATE evaluation follow these steps:

CALCULATE evaluates all the explicit filter arguments in the original evaluation context, each one independently from the others. This includes both the original row contexts (if any) and the original filter context. Once this evaluation is finished, CALCULATE starts building the new filter context.
CALCULATE makes a copy of the original filter context to prepare the new filter context. It discards the original row contexts, because the new evaluation context will not contain any row context.
CALCULATE performs the context transition. It uses the current value of columns in the original row contexts to provide a filter with a unique value for all the columns currently being iterated in the original row contexts. This filter may or may not contain one individual row. There is no guarantee that the new filter context contains a single row at this point. If there are no row contexts active, this step is skipped. Once all implicit filters created by the context transition are applied to the new filter context, CALCULATE moves on to the next step.
CALCULATE evaluates the CALCULATE modifiers used in filter arguments: USERELATIONSHIP, CROSSFILTER, ALL, ALLEXCEPT, ALLSELECTED, and ALLNOBLANKROW. This step happens after step 3. This is very important, because it means that one can remove the effects of the context transition by using ALL as a filter argument. The CALCULATE modifiers are applied after the context transition, so they can alter the effects of the context transition.
CALCULATE applies the explicit filter arguments evaluated at 1. to the new filter context generated after step 4. These filter arguments are applied to the new filter context once the context transition has happened so they can overwrite it, after filter removal — their filter is not removed by any ALL* modifier — and after the relationship architecture has been updated. However, the evaluation of filter arguments happens in the original filter context, and it is not affected by any other modifier or filter within the same CALCULATE function. If a filter argument is modified by KEEPFILTERS, the filter is added to the filter context without overwriting existing filters over the same column(s).
The filter context generated after point (5) is the new filter context used by CALCULATE in the evaluation of its expression.

docs.microsoft.com

Boolean filter expressions
A Boolean expression filter is an expression that evaluates to TRUE or FALSE. There are several rules that they must abide by:

They can reference columns from a single table.
They cannot reference measures.
They cannot use a nested CALCULATE function.
Beginning with the September 2021 release of Power BI Desktop, the following also apply:

They cannot use functions that scan or return a table unless they are passed as arguments to aggregation functions.
They can contain an aggregation function that returns a scalar value. For example,
DAX

Copy
Total sales on the last selected date =
CALCULATE (
SUM ( Sales[Sales Amount] ),
'Sales'[OrderDateKey] = MAX ( 'Sales'[OrderDateKey] )
)
Table filter expression
A table expression filter applies a table object as a filter. It could be a reference to a model table, but more likely it's a function that returns a table object. You can use the FILTER function to apply complex filter conditions, including those that cannot be defined by a Boolean filter expression.

Filter modifier functions
Filter modifier functions allow you to do more than simply add filters. They provide you with additional control when modifying filter context.

Function Purpose
REMOVEFILTERS Remove all filters, or filters from one or more columns of a table, or from all columns of a single table.
ALL 1, ALLEXCEPT, ALLNOBLANKROW Remove filters from one or more columns, or from all columns of a single table.
KEEPFILTERS Add filter without removing existing filters on the same columns.
USERELATIONSHIP Engage an inactive relationship between related columns, in which case the active relationship will automatically become inactive.
CROSSFILTER Modify filter direction (from both to single, or from single to both) or disable a relationship.
1 The ALL function and its variants behave as both filter modifiers and as functions that return table objects. If the REMOVEFILTERS function is supported by your tool, it's better to use it to remove filters.

Return value
The value that is the result of the expression.

Remarks
When filter expressions are provided, the CALCULATE function modifies the filter context to evaluate the expression. For each filter expression, there are two possible standard outcomes when the filter expression is not wrapped in the KEEPFILTERS function:

If the columns (or tables) aren't in the filter context, then new filters will be added to the filter context to evaluate the expression.
If the columns (or tables) are already in the filter context, the existing filters will be overwritten by the new filters to evaluate the CALCULATE expression.
The CALCULATE function used without filters achieves a specific requirement. It transitions row context to filter context. It's required when an expression (not a model measure) that summarizes model data needs to be evaluated in row context. This scenario can happen in a calculated column formula or when an expression in an iterator function is evaluated. Note that when a model measure is used in row context, context transition is automatic.

This function is not supported for use in DirectQuery mode when used in calculated columns or row-level security (RLS) rules.

[Greg_Deckler]

@CCDLA Sorry, forgot the ALL (or ALLSELECTED). So this doesn't work?

PreviousExtract = 
  VAR __Date = MAX(Dim_Calendar[Date])
  VAR __Table = FILTER(ALLSELECTED(Dim_Calendar), [BillingDay] = 1 && [Date] < __Date)
RETURN
  MAXX(__Table,[Date])

The [BillingDay] = 1 should preserve only the rows in the table where the [BillingDay] = 1.

[Greg_Deckler]

@CCDLA Try:

PreviousExtract = 
  VAR __Date = MAX(Dim_Calendar[Date])
  VAR __Table = FILTER(Dim_Calendar, [BillingDay] = 1 && [Date] < __Date)
RETURN
  MAXX(__Table,[Date])