DAX Query Optimization | Rolling customer % by feature and Period selection

[SergioTorrinha]

Hi all,

In my data model, I have a fact table (named facRawDataPD_cat ) with the format below:

dateID          custID  variable     value

01/07/2020	1	variable1	variable1_category_1
01/07/2020	2	variable1	variable1_category_2
01/07/2020	3	variable1	variable1_category_3
01/07/2020	4	variable1	variable1_category_4
01/07/2020	3	variable2	variable2_category_1
01/07/2020	1	variable2	variable2_category_2
01/07/2020	2	variable2	variable2_category_3
01/07/2020	4	variable2	variable2_category_3
01/07/2020	2	variable3	variable3_category_1
01/07/2020	3	variable3	variable3_category_2
01/07/2020	1	variable3	variable3_category_3
01/07/2020	4	variable3	variable3_category_4
02/07/2020	6	variable1	variable1_category_1
02/07/2020	5	variable1	variable1_category_2
02/07/2020	7	variable1	variable1_category_3
02/07/2020	8	variable1	variable1_category_4
02/07/2020	6	variable2	variable2_category_1
02/07/2020	5	variable2	variable2_category_2
02/07/2020	7	variable2	variable2_category_3
02/07/2020	8	variable2	variable2_category_3
02/07/2020	7	variable3	variable3_category_1
02/07/2020	5	variable3	variable3_category_2
02/07/2020	8	variable3	variable3_category_3
02/07/2020	6	variable3	variable3_category_4
03/07/2020	9	variable1	variable1_category_1
03/07/2020	10	variable1	variable1_category_2
03/07/2020	11	variable1	variable1_category_3
03/07/2020	12	variable1	variable1_category_4
03/07/2020	10	variable2	variable2_category_1
03/07/2020	11	variable2	variable2_category_2
03/07/2020	9	variable2	variable2_category_3
03/07/2020	12	variable2	variable2_category_1
03/07/2020	11	variable3	variable3_category_1
03/07/2020	10	variable3	variable3_category_2
03/07/2020	12	variable3	variable3_category_3
03/07/2020	9	variable3	variable3_category_4

and, of course i have also a calendar/dates table (named dimDates) that connects to this fact table by the dateID.

I have built the following metric to return the rolling % of customer by variable category across time (dates in this case) and according a certain amount of days, which the user can select to calculate - for example, a user might want to know how was the rolling customer% in the last 7 days for each variable category, in other cases the user might want to know how was the same figure for the last 15 days and so on. I also have a seperate table for this (names dimPeriods). Also, the user is alowed to select the variable (in a slicer) that he wants to analyse:

% Customers Selected Feature = 
VAR SelDays =
    SELECTEDVALUE ( dimPeriods[Days] )
VAR MaxDate =
    LASTDATE ( facRawDataPD_cat[dateID] )
VAR SelFeature = [SelectedFeature]

RETURN
    DIVIDE (
        CALCULATE (
            COUNT ( facRawDataPD_cat[value] ),
            facRawDataPD_cat[variable] = SelFeature,
            DATESINPERIOD ( dimDates[Date], LASTDATE ( dimDates[Date] ), - SelDays, DAY ),
            dimDates[Date] <= MaxDate
        ),
        CALCULATE (
            COUNT ( facRawDataPD_cat[value] ),
            facRawDataPD_cat[variable] = SelFeature,
            ALL ( facRawDataPD_cat[value] ),
            DATESINPERIOD ( dimDates[Date], LASTDATE ( dimDates[Date] ), - SelDays, DAY ),
            dimDates[Date] <= MaxDate
        )
    )

As you can see, the metric has the following parameters:

- a period - which consists in the number of days the user want the calculations done

- a variable selection - which consists in the names of the variables in the fact table

- a Maximum date - which consists in the maximum date for whitch there is data available for calculation

This metric, although is returning correct results, its a bit slow and I would like to speed it up but don't know where to start to optimize it.

Before coming for your help, I have tried to "pull out" the

DATESINPERIOD ( dimDates[Date], LASTDATE ( dimDates[Date] ), - SelDays, DAY ),
            dimDates[Date] <= MaxDate

but then the results weren't correct.

So, my question is, how can I optimize this metric given it is used to build a nice line chart with the dynamics (Period + variable selections) ?

Thanks in advance and sorry if the post is a little confusing 🙂

[SergioTorrinha · Accepted solution]

Hi all!

I just solved this problem myself 🙂

It turns out that it was

dimDates[Date] <= MaxDate

that was turning the query slow.
So, to solve this, what I did was to create a calculated column (yes, I know calculated columns are bad to use! 😛 ) in my dimDates that returns true whenever I have dates in my fact table, so I basicly passed the above exression to a calculated column. After that, i removed the above expression from my metric and I filtered my visual by the calculated column = True.

Please let me know if there is some better way to do this, but aparently my dax query is pretty quick now. 🙂
Thanks.