Power BI: Dynamically Computed Grouped Averages - Can I speed this up any?

Question

I have a group of "Loyalty" members who come to our stores and spend money. This problem is easily handled in Excel, or even in SQL databases, but I'm having an issue finding a solution in Power BI. I want my users to be able to select a date range & a location & some other demographic slicer (like age band). I then want to compute a customers average daily spend (ADS) and monthly spend (AMS) and group them in buckets. Lastly, I want to display various KPIs (unique guests, revenue, trips, etc) by these created buckets.

I have tried creating new tables, but the tables are not dynamic in that an individuals ADS & AMS will only re-compute whenever the dataset is refreshed. We have been told to only use the "Import". Direct Query is not a valid solution for us. I have tried calculated columns, but again, same issue as tables. The columns will not re-compute with date selections. I even tried getting fancy with DAX and setting Min/Max dates to re-compute, but this doesn't work either because a calculated column does not have a reference point to a slicer from a report page.

Keep in mind I do have this working. The issue is the amount of time it takes to complete. Even just adjusting the date slicer by 1 day will take this visual more than 5 minutes to complete. I don't think that will bode well with my end users. My model is Star Schema and only has 1 to Many relationship and NONE have been set to bidirectional. The table containing the values for the AMS/ADS bands do not have any relationships to other tables.

Here is my Measure:

Rev_AMS = 
VAR T1 = 
SUMMARIZE(
    Sales_Fact,
    Loyalty_Table[Loyalty_Key], Calendar_Table[Fiscal_Period_No],
    "AvgMonthSpend", [AverageMonthlySpend]
)
VAR Result =
IF(
    HASONEVALUE(AMS_Bands[AMS Band]),
    SWITCH(
        VALUES(AMS_Bands[AMS Band]),
        "<$0", CALCULATE([Rev], FILTER(T1, [AvgMonthSpend] < 0)),
        "$0-$15", CALCULATE([Rev], FILTER(T1, [AvgMonthSpend] >= 0 && [AvgMonthSpend] < 15)),
        "$15-$30", CALCULATE([Rev], FILTER(T1, [AvgMonthSpend] >= 15 && [AvgMonthSpend] < 30)),
        "$30-$50", CALCULATE([Rev], FILTER(T1, [AvgMonthSpend] >= 30 && [AvgMonthSpend] < 50)),
        "$50-$75", CALCULATE([Rev], FILTER(T1, [AvgMonthSpend] >= 50 && [AvgMonthSpend] < 75)),
        "$75-$100", CALCULATE([Rev], FILTER(T1, [AvgMonthSpend] >= 75 && [AvgMonthSpend] < 100)),
        "$100-$150", CALCULATE([Rev], FILTER(T1, [AvgMonthSpend] >= 100 && [AvgMonthSpend] < 150)),
        "$150-$200", CALCULATE([Rev], FILTER(T1, [AvgMonthSpend] >= 150 && [AvgMonthSpend] < 200)),
        "$200-$300", CALCULATE([Rev], FILTER(T1, [AvgMonthSpend] >= 200 && [AvgMonthSpend] < 300)),
        "$300+", CALCULATE([Rev], FILTER(T1, [AvgMonthSpend] >= 300))
    ),
    [Rev]
)
RETURN Result

The other measures feeding into this one are:

Rev = SUM(Sales_Fact[Revenue])

AverageMonthlySpend = DIVIDE([Rev],[MonthlyTrip],BLANK())

MonthlyTrip = 
Var T = 
SUMMARIZE(
    Sales_Fact,
    Calendar_Table[Fiscal_Period_No], Sales_Fact[Organization_Key],
    "Trip", [UniqueGuest]
)
Var Result = 
SUMX(
    T,
    [Trip]
)
RETURN Result

UniqueGuest = DISTINCTCOUNT(Sales_Fact[Loyalty_Key])

My question: Is there a better/faster way to achieve this?

In order to be a valid solution, I need to maintain the dynamic computation of ADS/AMS based on user slicer selections while speeding up the computation (preferably significantly but I'll take what I can get).

Thank you in advance.

Answer 1

I can't use comments as that will limit the readability so have to add another answer:

Here are some suggestions that you should always follow.

1. Only use SUMMARIZE to do groupby operation, don't use it to add new columns, it is a sub optimal function and almost always generates more complex query plans with more bigger data caches, always use ADDCOLUMNS to add new columns. Here is an image showing the difference https://ibb.co/1Q7SSgB (Pay attention to the rows reported by the data caches under the Rows header)
2. Most of the time you are only interested in knowing if the column used inside HASONEVALUE is being used for grouping, so simply replace that with more optimized ISINSCOPE, please note both have different use cases, but in most cases ISINSCOPE is the way to go
3. DISTINCTCOUNT is a non-additive measure and is always a complex function for any kind of analytical tool, when the filter context changes frequently the DAX engines generates so many Storage Engine queries that it slows down the performance, on the other hand SUMX is faster as it will get a single data cache out of the Storage Engine and then the Formula Engine will iterate on it thus saving a lot of time.

Here is a post that I solved few months ago that will give you idea of why SUMX construct is faster than DISTINCTCOUNT https://forum.enterprisedna.co/t/dax-calculation-based-on-previous-day-and-a-constant-value/14948/2

Answer 2

Try these measures and let me know if that has improved the performance:

Rev_AMS =
VAR T1 =
    ADDCOLUMNS (
        SUMMARIZE (
            Sales_Fact,
            Loyalty_Table[Loyalty_Key],
            Calendar_Table[Fiscal_Period_No]
        ),
        "AvgMonthSpend", [AverageMonthlySpend]
    )
VAR Result =
    IF (
        ISINSCOPE ( AMS_Bands[AMS Band] ),
        SWITCH (
            SELECTEDVALUE ( AMS_Bands[AMS Band] ),
            "<$0", CALCULATE ( [Rev], FILTER ( T1, [AvgMonthSpend] < 0 ) ),
            "$0-$15",
                CALCULATE ( [Rev], FILTER ( T1, [AvgMonthSpend] >= 0 && [AvgMonthSpend] < 15 ) ),
            "$15-$30",
                CALCULATE (
                    [Rev],
                    FILTER ( T1, [AvgMonthSpend] >= 15 && [AvgMonthSpend] < 30 )
                ),
            "$30-$50",
                CALCULATE (
                    [Rev],
                    FILTER ( T1, [AvgMonthSpend] >= 30 && [AvgMonthSpend] < 50 )
                ),
            "$50-$75",
                CALCULATE (
                    [Rev],
                    FILTER ( T1, [AvgMonthSpend] >= 50 && [AvgMonthSpend] < 75 )
                ),
            "$75-$100",
                CALCULATE (
                    [Rev],
                    FILTER ( T1, [AvgMonthSpend] >= 75 && [AvgMonthSpend] < 100 )
                ),
            "$100-$150",
                CALCULATE (
                    [Rev],
                    FILTER ( T1, [AvgMonthSpend] >= 100 && [AvgMonthSpend] < 150 )
                ),
            "$150-$200",
                CALCULATE (
                    [Rev],
                    FILTER ( T1, [AvgMonthSpend] >= 150 && [AvgMonthSpend] < 200 )
                ),
            "$200-$300",
                CALCULATE (
                    [Rev],
                    FILTER ( T1, [AvgMonthSpend] >= 200 && [AvgMonthSpend] < 300 )
                ),
            "$300+", CALCULATE ( [Rev], FILTER ( T1, [AvgMonthSpend] >= 300 ) )
        ),
        [Rev]
    )
RETURN
    Result

MonthlyTrip =
VAR T =
    ADDCOLUMNS (
        SUMMARIZE (
            Sales_Fact,
            Calendar_Table[Fiscal_Period_No],
            Sales_Fact[Organization_Key]
        ),
        "Trip", [UniqueGuest]
    )
VAR Result =
    SUMX ( T, [Trip] )
RETURN
    Result

UniqueGuest =
SUMX (
    DISTINCT ( Sales_Fact[Loyalty_Key] ),
    1
)