DATESINPERIOD DAX Measure Taking 13 Seconds to Run, Is Another Time Intelligence Function Better?

[mohamedmutter]

I have a DAX Query that performance analyzer is telling me is taking roughly 12-15 seconds which is obviously no good. I am attempting to calculate the number of player loads a publisher has had since their "live date" / onboarding date in increments of 15/30/45/60/90 days. Essentially little checkpoints since they became a customer. 

So I want the SUM(player loads) between their live date and 15 days after, between live date and 30 days after and so on. I have a calendar table (Screenshotted below) that contains all dates since 2014 - present. My table that has all the publisher live dates does not contain a full list of all dates, only the dates they went live. I have a publisher key table using c_id that is joining my fact table to my live date table that is using one to many relationships.

Here is my DAX:

Player Loads 15 Days =
CALCULATE (
    SUM ( FACT_bq_dp_iir_smp_summary[Player Loads] ),
    DATESINPERIOD (
        DIM_Calendar[Date],
        MAX ( 'Pub Live Dates'[Live Date] ),
        +15,
        DAY
    )

When i put this DAX along with the 4 other measures for +30/45/60/90 days, the time to process is roughly 12-15 seconds. Is this the most DAX efficient way to write this? Or is there some other method that could be better?

Calendar Table

Player Loads Fact Table

Customer Live Date

Dax Query Performance

[PaulOlding]

Hi @mohamedmutter 

Here's an alternative way to calculate this.

Player Loads 15 Days = 
SUMX(
	FILTER(FACT_bq_dp_iir_smp_summary, FACT_bq_dp_iir_smp_summary[Date] - RELATED('Pub Live Dates'[Live Date]) < 15),
	FACT_bq_dp_iir_smp_summary[Player Loads]
	)

Worth trying to see if it's any quicker.  It's resolved with a single storage engine query so may give you better performance.

[mohamedmutter]

@PaulOlding I've tried the above, but in the RELATED('Pub Live Dates'[Live Date]) section ... it won't let me actually select that field for the measure. 

[PaulOlding]

@mohamedmutter what are the relationships between the tables in the model?

[mohamedmutter]

Here's an image of my relationships. Basically I have a key table for my publishers using c_id which links my main fact table through my pub live date table. And then the main date fields (live date for pub_live_date) all link to my calendar table. 

[Greg_Deckler]

@mohamedmutter Yeah, with that setup, Pub Live Dates does not have a relationship with your fact table so basically you are getting "all". So if you have millions of rows in there that could take some time. 

You could potentially solve this with CROSSFILTER or USERELATIONSHIP potentially. Create an inactive relationship between your tables, for example based on c_id. Another thought is it would be intereting to see how long this takes:

  VAR __Date = MAX ( 'Pub Live Dates'[Live Date] )
  VAR __Date15 = __Date + 15
  VAR __Table =     FILTER (
        FACT_bq_dp_iir_smp_summary,
        [date] >= __Date && [date] <= __Date15
    )
RETURN
    SUMX ( __Table, [Player Loads] )

Or


Player Loads 15 Days =
  VAR __Date = MAX ( 'Pub Live Dates'[Live Date] )
  VAR __Date15 = __Date + 15
RETURN
CALCULATE (
    SUM ( FACT_bq_dp_iir_smp_summary[Player Loads] ),
    FILTER (
        FACT_bq_dp_iir_smp_summary,
        [date] >= __Date && [date] <= __Date15
    )
)

[Greg_Deckler]

@mohamedmutter Why not:

Player Loads 15 Days =
  VAR __Date = MAX ( 'Pub Live Dates'[Live Date] )
  VAR __Date15 = __Date + 15
RETURN
CALCULATE (
    SUM ( FACT_bq_dp_iir_smp_summary[Player Loads] ),
    FILTER (
        DIM_Calendar[Date],
        [Date] >= __Date && [Date] <= __Date15
    )
)

[mohamedmutter]

@Greg_Deckler I tried yours, and somehow with just that measure in the table and nothing else, it's up to almost 70 seconds to load now. Does it matter that I have BLANK dates in the pub live dates table that could be tripping this up?

[Greg_Deckler]

@mohamedmutter Well, something is clearly not right. I am guessing that you have some sort of data modeling issue going on at the heart of all of this. What does your data model look like?

[mohamedmutter]

@Greg_Deckler 

All my relationships are one --> many. 

I have a main fact table where I'm getting my player loads, that is linking by an id field to my publisher table. That publisher table links to to my publisher live dates table via an id field. Then both the publisher live dates table and my main fact table are linking to a calendar table. 

My main fact table in the service will be pushing roughly 100 mm rows though. But in my desktop app I've limited it via a Keep Top Rows parameter to 15 million. One other strange note, is that when trying to refresh this 15 mm row data set in the service, I'm getting an error with our 32 GB RAM Gateway running out of memory during the process. This wasn't happening before I added these measures in.