Crossfilter customer table to improve performance

[Linger]

Hi there 😀

I am currently working with a data model that has about 40 million rows in the fact table and about 300k rows in the customer table.

The measure that is keeping things interesting is supposed to return the number of active customers. These are defined by sales in the last 12 months to be greater than 0.

This was fairly easy to do however I wasn't happy with the performance. My guess was that the formula was slow even if I only looked at one country because it has to iterate through the entire customer table regardless of other filters.

My measure looks more or less like this:

No of Active Customers 12M =
CALCULATE (
         COUNTROWS ( CustomerTable ),
         FILTER ( CustomerTable, [Sales 12M] > 0 )
)

I thought it would be good not to iterate through the entire customer table so I added this variable to pre-filter it:
VAR _CustomerTable =
CALCULATETABLE (
        CustomerTable,
        CROSSFILTER ( FactTable[customerid], CustomerTable[customerid], BOTH )
)

This worked great at the beginning and still does when I look at full years.

However this measure is also required to work on a monthly level and when I filter for let's say September 2020 the CROSSFILTER works as it should but filters out customers from my customer table that didn't have any sales in that month. As my measure is based on rolling 12 months this shouldn't happen.

What I would need is some kind of ALLEXCEPT so CROSSFILTER ignores the date and returns all customers for the selected country for example but I am not sure if that's possible.

Is there perhaps another approach I am not thinking of? As I said my main concern is performance.

Thank you very much in advance for your help 😊

[CNENFRNL]

Hi, @Linger , from you description, it's inferred that CustomerTable is a lookup table; and a CustomerTable(1):FactTable(*) relationship is already created.

I personally prefer expanded table to any other dynamic means of relationship (CROSSFILTER, TREATAS etc,) in terms of performance and reliability; by this means, my measure will be authored this way,

# Active Customers 12M =
VAR __fact =
    FILTER (
        ALL ( FactTable ),
        FactTable[Date]
            IN DATESINPERIOD ( DimDate[Date], MAX ( DimDate[Date] ), -12, MONTH )
    )
RETURN
    COUNTROWS ( CALCULATETABLE ( CustomerTable, _fact ) )

Due to lacking a full picture of your data model, I derive such a measure from one of my old projects (100k rows, performance plays a minor important role). You may want to tweak it according to your data model. It would be great if you can tell me about its performance.

[amitchandak · Accepted solution]

@Linger , refer if my these blogs can help

https://community.powerbi.com/t5/Community-Blog/Customer-Retention-Part-1-Month-on-Month-Retention/b...

https://community.powerbi.com/t5/Community-Blog/Customer-Retention-Part-2-Period-over-Period-Retenti...

[Linger]

@amitchandak Thank you very much for referring to your blog posts. These lead me to a solution I am happy with 😊

I tried your version with SUMX which worked fine and gave me a new way to approach my measure.

It is slightly slower than the measure I posted previously but as this one returns incorrect results on the month level I can't use it anyway.

What also worked was slimply replacing CustomerTable by VALUES(CustomerTable[customerid]). Not sure why I didn't try that in the first place. This way the measure is as fast as the SUMX version.

@CNENFRNL Thank you very much for taking the time to explain your approach. With some tweaking I think this could also work fine for No of Active Customers. However I also need other versions of this measure considering Sales 12M > X so I prefer to reference the measure.