Calculating member sales - using Fact vs Dim DAX query

[anandav]

Hi All,

I have the below model.

Requirement: Calculate the sales of Club Members for the Last 12 Months.

DAX query 1 (using Customer Dim): Server Timings  4579 ms FE:24%  SE:76%

DEFINE
    VAR Results =
        CALCULATE (
            DISTINCTCOUNTNOBLANK ( Sales[Customer#] ),
            DISTINCT ( ClubMember[Customer#] ),
            CalendarOptions[Date Options] = "Last 12 Months"
        )
EVALUATE
{ Results }

DAX query 2 (using only Fact) : Server Timings  625 ms FE:0%  SE:100%

DEFINE
    VAR Results =
        CALCULATE (
            DISTINCTCOUNTNOBLANK ( Sales[Customer#] ),
            Sales[MemberTypeCode] = 1,
            CalendarOptions[Date Options] = "Last 12 Months"
        )
EVALUATE
{ Results }

DAX query 3 (using DIM Club Member Type): Server Timings  625 ms FE:0%  SE:100%

DEFINE
    VAR Results =
        CALCULATE (
            DISTINCTCOUNTNOBLANK ( Sales[Customer#] ),
            ClubMemberTypes[MemberTypeCode] = 1,
            CalendarDateOptions[Date Options] = "Last 12 Months"
        )
EVALUATE
{ Results }

Question:

What is the recommended approach in star schema model - using Club Member Dim table to calculate club member sales or having a column in Fact table to indicate Club Member (additional column) and having a Club Member Type Dim table?

In DAX, calculating member sales using Club Member Dim table is time consuming (nearly 5 seconds) vs having an additional column in Fact table and using Club Member Type Dim table (625 ms).

I understand the query plan for using Club Member Dim table needs to retrieve 2 Million records first - so it takes time.

I wanted to know whether it is better to have an additional column in Fact table to optimise query compared to the storage/memmory cost of the additional column in Fact table? (150 million rows)

Or is there any way to optimise the query that is using the Club Member Dim table - Query 1?

Thanks in advance.

[anandav]

Thanks @amitchandak .

The requirement is calculating sales for members who made pruchases. I have the DAX aueries but I wanted to know which would be a better approach.

[amitchandak]

@anandav , with help from date table you can get last 12 month of data like

Rolling 12 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date ],MAX('Date'[Date ]),-12,MONTH))

You should use club memeber only in group by on in measure as filter or group by like in values. Calculation columns you should prefer from the fact (count/sum etc)