Use CalculateTable as reference for measure

[ciken]

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. 

Here is my original measure:

# Active Customers =

VAR ActiveDate =

    CALCULATE (

        MAX(CalendarTable[Dates]),

        ALLSELECTED ( CalendarTable[Dates] )

    )

VAR CustomersWithActiveDate =

    CALCULATETABLE (                        -- Prepares a table that

        ADDCOLUMNS (                        -- for each customer contains

            SUMMARIZE('Line Items',

            'Line Items'[customer_id],

            'Line Items'[Brand])

            ,  -- the date when they are considered active until

            "@ActiveThrough", [Active Through]

        ),

                ALLSELECTED ( 'Customer Table' ),       -- Regardless of local filters on Customer

        CalendarTable[Dates]<= ActiveDate,

            'Line Items'[Order Status] IN {"Shipped", "Authorized", "Captured", "Completed", "Partially Refunded"}

            &&'Line Items'[AS vs SO]="Autoship"-- and on Date

    )

VAR ActiveCustomers =

    FILTER (                              

        CustomersWithActiveDate,          -- Filters the customers

        [@ActiveThrough]

            >=ActiveDate  -- falls within the current time period

        )

VAR Result =

    COUNTROWS ( ActiveCustomers )         -- The count of the active customers does not

                                        -- use the Sales table (no sales in the period)

RETURN

Result


I moved the entire VAR CustomersWithActiveDate to it's own calculated table and then replaced Active Customers with 

FILTER (                              

        'Subscription',          -- Filters the customers

        [@ActiveThrough]

            >=ActiveDate  -- falls within the current time period

        )

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. 

Any advice? What am I missing?

[ciken]

@lbendlin 

Hoping this is a small enough file to give you data enough to help.

Thanks!
Corey

Active Subscribers.pbix

[lbendlin]

A calculated table outside a measure no longer has filter context. That means things like "ALLSELECTED"  become meaningless. 

You will have to slightly shift your approach to create that calculated table taking the entire data model into account.  The positive part is that you only have to do that once, and your measures will then benefit from the performance gain.

[ciken]

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]

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-...

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/1447...