CODEWITHSUNDEEP
ssasdaxssas-tabular
Ross
Ross

Reputation: 3

Creating a calculated column (not aggregate) that changes value based on context SSAS tabular DAX

Data: I have a single row that represents an annual subscription to a product, it has an overall startDate and endDate, there is also third date which is startdate + 1 month called endDateNew. I also have a non-related date table (called table X).

Output I'm looking for: I need a new column called Categorisation that will return 'New' if the date selected in table X is between startDate and endDateNew and 'Existing' if the date is between startDate and endDate.

Problem: The column seems to evaluate immediately without taking in to account the date context from the non-related date table - I kinda expected this to happen in visual studio (where it assumes the context is all records?) but when previewing in Excel it carries through this same value through.

The bit that is working:I have an aggregate (an active subscriber count) that correctly counts the subscription as active over the months selected in Table X.

The SQL equivalent on an individual date:

case 
 when '2015-10-01' between startDate and endDateNew then 'New'
 when '2015-10-01' < endDate then 'Existing'
end as Category

where the value would be calculated for each date in table X

Thanks!

Ross

Upvotes: 0

Views: 1571

Answers (2)

daniel
daniel

Reputation: 55

Ross,

Calculated columns work differently than Excel. Optimally the value is known when the record is first added to the model. Your example is kinda similar to a slowly changing dimension .

There are several possible solutions. Here are two and a half:

Full process on the last 32 days of data every time you process the subscriptions table (which may be unacceptably inefficient).

OR

Create a new table 'Subscription scd' with the primary key from the subscriptions table and your single calculated column of 'Subscription Age in Days'. Like an outrigger. This table could be reprocessed more efficiently than reprocessing the subscriptions table, so process the subscriptions table as incrementals only and do a full process on this table for the data within the last 32 days instead.

OR

Decide which measures are interesting within the 'new/existing' context and write explicit measures for them using a dynamic filter on the date column in the measures

eg. Define 

'Sum of Sales - New Subscriptions',
'Sum of Sales - Existing Subscriptions', 
'Distinct Count of New Subscriptions - Last 28 Days', etc

Upvotes: 0

greggyb
greggyb

Reputation: 3798

Calculated columns are only evaluated at model refresh/process time. This is by design. There is no way to make a calculated column change based on run-time changes in filter context from a pivot table.

Upvotes: 1

Related Questions