Use CalculateTable as reference for measure

Question

Good afternoon. It was suggested to me to take a measure and remove the VAR for calculated table and create an actual Calculated Table then reference that for the remainder. It would help reduce the processing time and provide quicker access to the data. However, when I took the Calculated Table section and moved it, I am struggling with trying it to a date table and getting the results I want.&nbsp;&nbsp;Here is my original measure:# Active Customers = VAR ActiveDate = &nbsp; &nbsp; CALCULATE (&nbsp; &nbsp; &nbsp; &nbsp; MAX(CalendarTable[Dates]),&nbsp; &nbsp; &nbsp; &nbsp; ALLSELECTED ( CalendarTable[Dates] )&nbsp; &nbsp; )VAR CustomersWithActiveDate =&nbsp; &nbsp; CALCULATETABLE ( &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;-- Prepares a table that &nbsp; &nbsp; &nbsp; &nbsp; ADDCOLUMNS ( &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;-- for each customer contains&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; SUMMARIZE('Line Items',&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 'Line Items'[customer_id],&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 'Line Items'[Brand])&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; , &nbsp;-- the date when they are considered active until&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; "@ActiveThrough", [Active Through]&nbsp; &nbsp; &nbsp; &nbsp; ),&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; ALLSELECTED ( 'Customer Table' ), &nbsp; &nbsp; &nbsp; -- Regardless of local filters on Customer&nbsp; &nbsp; &nbsp; &nbsp; CalendarTable[Dates]&lt;= ActiveDate, &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 'Line Items'[Order Status] IN {"Shipped", "Authorized", "Captured", "Completed", "Partially Refunded"}&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &amp;&amp;'Line Items'[AS vs SO]="Autoship"-- and on Date&nbsp; &nbsp; )VAR ActiveCustomers =&nbsp; &nbsp; FILTER ( &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp; &nbsp; &nbsp; &nbsp; CustomersWithActiveDate, &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;-- Filters the customers&nbsp; &nbsp; &nbsp; &nbsp; [@ActiveThrough]&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &gt;=ActiveDate &nbsp;-- falls within the current time period&nbsp; &nbsp; &nbsp; &nbsp; )VAR Result =&nbsp; &nbsp; COUNTROWS ( ActiveCustomers ) &nbsp; &nbsp; &nbsp; &nbsp; -- The count of the active customers does not&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; -- use the Sales table (no sales in the period)RETURNResultI moved the entire&nbsp;VAR CustomersWithActiveDate to it's own calculated table and then replaced Active Customers with&nbsp;FILTER ( &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp; &nbsp; &nbsp; &nbsp; 'Subscription', &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;-- Filters the customers&nbsp; &nbsp; &nbsp; &nbsp; [@ActiveThrough]&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &gt;=ActiveDate &nbsp;-- falls within the current time period&nbsp; &nbsp; &nbsp; &nbsp; )But my results put a tick mark in every month prior to their first order and I can't seem to build a relationship between the ActiveThrough and DateTable.&nbsp;Any advice? What am I missing?

lbendlin · Answer

A calculated table outside a measure no longer has filter context. That means things like "ALLSELECTED"&nbsp; become meaningless.&nbsp;
&nbsp;
You will have to slightly shift your approach to create that calculated table taking the entire data model into account.&nbsp; The positive part is that you only have to do that once, and your measures will then benefit from the performance gain.

ciken · Answer

Thanks - do you have a suggestion of how to do that? I can obviously remove the Variable around the calendar as well as the AllSelected on the customer table.

But then when I go to write the measure - how do I ensure it works properly with that calculatetable pulled out?

lbendlin · Answer

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).

Do not include sensitive information or anything not related to the issue or question.

If you are unsure how to upload data please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-p/963216

Please show the expected outcome based on the sample data you provided.

Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523

ciken · Answer

lbendlin&nbsp;Hoping this is a small enough file to give you data enough to help.Thanks!CoreyActive Subscribers.pbix

Forum Discussion

Use CalculateTable as reference for measure

4 Replies

Related content

Reference a measure in another measure

FILTER vs CALCULATETABLE

CalculateTable

dynamic reference line

How to reference column in table variable (after TOPN and CALCULATETABLE)?