code that works in a measure doesn't work in a calculated field

[bsheffer]

thanks to everyone who has responded to my earlier requests on this dashboard.  It is been a huge learning experience for me.

I have 3 tables

dim view_mid_properties -- holds merchant id and other details.  related by merchant_number to the status_history table

     merchant_number

     _consecutive_months_seasonal  -- this is the calcuated field I want to add

fact mid_status_history -- holds status by month for each merchant

     merchant_number

     activity_month

     activity_date

     change_details_from -- holds the status for a month (there may be more than one record for a month with different status values

Dim Calendar ActivityMonth -- calendar table related to the fact mid_status_history on activity_date

date

actuals_relative_month_flag  has a value from 1 to -36 (1 being the current month, 0 is last month, and -1 to -36 for further prior months)

consecutive months - each full month from the most recent month that a merchant was marked "Seasonal".  If there is another status that month then it doesn't not count as seasonal.

the calculated field:

Initially I checked for the presence of Seasonal and Non-Seasonal for each month for each month from the current month and returning the value of the number of consecutive months seasonal.  a merchant_number would have a value of 0 to -32 (based on the number of activity months in the status_history month currently).  

based on my code it seems to me that the merchant_number in fact mid_status_history is being filtered on the merchant_number in dim view_mid_properties.

if(calculate(DISTINCTCOUNT('Fact MID_Status_History'[ACTIVITY_MONTH]), filter(all('Dim Calendar ActivityMonth'), [_ACTUALS RELATIVE MONTH FLAG] = 1), 'Fact MID_Status_History'[CHANGE_DETAILS_FROM] = "Seasonal") = 0
|| calculate(DISTINCTCOUNT('Fact MID_Status_History'[ACTIVITY_MONTH]), filter(all('Dim Calendar ActivityMonth'), [_ACTUALS RELATIVE MONTH FLAG] = 1), 'Fact MID_Status_History'[CHANGE_DETAILS_FROM] <> "Seasonal") = 1
, 0,
if(calculate(DISTINCTCOUNT('Fact MID_Status_History'[ACTIVITY_MONTH]), filter(all('Dim Calendar ActivityMonth'), [_ACTUALS RELATIVE MONTH FLAG] = 0), 'Fact MID_Status_History'[CHANGE_DETAILS_FROM] = "Seasonal") = 0
|| calculate(DISTINCTCOUNT('Fact MID_Status_History'[ACTIVITY_MONTH]), filter(all('Dim Calendar ActivityMonth'), [_ACTUALS RELATIVE MONTH FLAG] = 0), 'Fact MID_Status_History'[CHANGE_DETAILS_FROM] <> "Seasonal") = 1
, 1,
if(calculate(DISTINCTCOUNT('Fact MID_Status_History'[ACTIVITY_MONTH]), filter(all('Dim Calendar ActivityMonth'), [_ACTUALS RELATIVE MONTH FLAG] = -1), 'Fact MID_Status_History'[CHANGE_DETAILS_FROM] = "Seasonal") = 0
|| calculate(DISTINCTCOUNT('Fact MID_Status_History'[ACTIVITY_MONTH]), filter(all('Dim Calendar ActivityMonth'), [_ACTUALS RELATIVE MONTH FLAG] = -1), 'Fact MID_Status_History'[CHANGE_DETAILS_FROM] <> "Seasonal") = 1
, 2,
...
38))))))))))))))))))))))))))))))))))))))

Later I decided to try this by finding the latest_month there was a non-seasonal status and subtracting it from the latest month there was a seasonal value to get the number of consecutive full months that a merchant was in seasonal status.

var _max_month = max('Fact MID_Status_History'[ACTIVITY_MONTH])

var _min_month = min('Fact MID_Status_History'[ACTIVITY_MONTH])

var _max_month_seasonal = calculate(maxx('Fact MID_Status_History', 'Fact MID_Status_History'[ACTIVITY_MONTH]),

filter('Fact MID_Status_History',

'Fact MID_Status_History'[CHANGE_DETAILS_FROM] = "Seasonal"))

var _max_month_non_seasonal = calculate(maxx('Fact MID_Status_History', 'Fact MID_Status_History'[ACTIVITY_MONTH]),

filter('Fact MID_Status_History',

'Fact MID_Status_History'[CHANGE_DETAILS_FROM] <> "Seasonal"))

return

if(_max_month_seasonal <> _max_month, 0,

if(_max_month_non_seasonal = BLANK(),

datediff(_min_month, _max_month_seasonal, MONTH) + 1,

datediff(_max_month_non_seasonal, _max_month_seasonal, MONTH)

)

)

while this worked fine as a measure in a table view by merchant_number.  It only returned 0 as a calculated field.

but when I added the merchant_number to the filter statements it worked as expected.

var _mid = 'Dim View_MID_Properties'[merchant_number] -- storing merchant_number
var _max_month = max('Fact MID_Status_History'[ACTIVITY_MONTH])
var _min_month = min('Fact MID_Status_History'[ACTIVITY_MONTH])
var _max_month_seasonal = calculate(maxx('Fact MID_Status_History', 'Fact MID_Status_History'[ACTIVITY_MONTH]),
filter('Fact MID_Status_History',
'Fact MID_Status_History'[CHANGE_DETAILS_FROM] = "Seasonal"
&& 'Fact MID_Status_History'[Merchant_Number] = _mid -- added to filter
))
var _max_month_non_seasonal = calculate(maxx('Fact MID_Status_History', 'Fact MID_Status_History'[ACTIVITY_MONTH]),
filter('Fact MID_Status_History',
'Fact MID_Status_History'[CHANGE_DETAILS_FROM] <> "Seasonal"
&& 'Fact MID_Status_History'[Merchant_Number] = _mid -- added to filter
))
return
if(_max_month_seasonal <> _max_month, 0,
if(_max_month_non_seasonal = BLANK(),
datediff(_min_month, _max_month_seasonal, MONTH) + 1,
datediff(_max_month_non_seasonal, _max_month_seasonal, MONTH)
)
)

Please explain why the original code worked but the 2nd version without the merchant_number in the filter failed to work.

[v-jingzhang]

Hi @bsheffer 

It is due to the different calculation context. Any DAX expression is evaluated in the context, which is considered the environment for it. To explain what is calculation context would be very long. I attach links of some blogs and articles about it at bottom for your reference. 

Now see a simple example for calculated columns. Calculated columns are affected by the row context in a table. They will not be influenced by filters and slicers in the report.

Max Value = MAX('Table'[Value])

Max Value 2 = 
VAR _name = 'Table'[Name]
RETURN
CALCULATE(MAX('Table'[Value]),'Table'[Name]=_name)

Max by Name = MAXX(FILTER('Table','Table'[Name]=EARLIER('Table'[Name])),'Table'[Value])

Max by Name 2 = CALCULATE(MAX('Table'[Value]),ALLEXCEPT('Table','Table'[Name]))

Measures are evaluated according to the context they are put in. A measure will return different results when they are in different visuals. In below example, when Max Measure is in a table singly, it is evaluated in the whole table. When you add Name column into it, Name column will provide a filter context to it automatically. 

So when you use the same code for a measure and a calculated column, you need to take their context into account. The difference of context will lead to different results. 

Context in DAX Formulas (microsoft.com)

Row Context and Filter Context in DAX - SQLBI

Power BI DAX Studio Tutorial: Context & The Calculation Engine (enterprisedna.co)

Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.

[bsheffer]

thanks for your response.  I was expecting the row context of the calculated field to provide an implicit filter to get the values I needed.  In my first version it did but in the second it did not.  I understand that page filters or slicers have no influence on the value in a calculated field.

So I still don't understand why I had to add && 'Fact MID_Status_History'[Merchant_Number] = _mid to the second calculated field definition when it wasn't required in the earlier method to get the same value.

[v-jingzhang · Accepted solution]

Hi @bsheffer 

The thing is that a calculated column cannot provide an implicit filter to it, so you need to add && 'Fact MID_Status_History'[Merchant_Number] = _mid to the column code. When you use the code in a measure without this filter and put the measure in a table visual, I guess you put merchant_number field into the same table visual too. It is the merchant_number field that provides an implicit filter to the measure.

You can test the values of each variable step by step in a measure and a calculated column. You will find that the first two variables var _max_month = max('Fact MID_Status_History'[ACTIVITY_MONTH]) and var _min_month = min('Fact MID_Status_History'[ACTIVITY_MONTH]) in a measure will return the maximum and minimum activity months of corresponding merchant_number. However, when they are in a calculated column, these two variables will return the maximum and minimum activity months of the whole activity_month column in status history table. Other variables have similar results. 

Another thing is that you didn't use ALL function in these code, so you notice the difference. If you add ALL into it, you may need to add a filter in the measure too. ALL function returns all the rows in a table, or all the values in a column, ignoring any filters that might have been applied. This function is useful for clearing filters and creating calculations on all the rows in a table.

If you don't want to add this filter, another method is to use ALLEXCEPT function. It returns all the rows in a table except for those rows that are affected by the specified column filters. Have a try.

ALLEXCEPT – DAX Guide

ALL - DAX Guide

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.

[bsheffer]

here's a snapshot of the table relations if that helps: