cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
davidcray75
Frequent Visitor

Selecting between slowing changing dimension effective dates is very slow

Hello, 

 

In a dimension table of 4 million records in my Power BI file using import mode, I have slowly changing rows for customer attributes that looks like this:

 

The date columns are actually datetime (there are no gaps or overlap).

 

customer keycustomer source keycustomer nameis activeeffective date starteffective date end
1aaacustomer 111/1/20191/31/2019
2aaacustomer 102/1/201912/31/2099
3bbbcustomer 211/1/202012/31/2021
4bbbcustomer 2a11/1/202112/31/2099
5ccccustomer 311/1/20206/30/2021
6ccccustomer 3a07/1/202112/31/2099

 

I want to know at any given time how many of these distinct customer source keys are active at that time. Example results using the data above would evaluate to:

 

1/14/2019 = 1 active customer source key

6/1/2019 = 0 active customer source keys

2/15/2021 = 2 active customer source keys

 

The following DAX filters a single column where it's value is between two dates. The results return very quickly with no issues or noticable latency; example:

 

Test =
var minDate = MIN('Date'[Calendar Date])
var maxDate = MAX('Date'[Calendar Date])
return CALCULATE(DISTINCTCOUNT('Table'[customer source key]),
'Table'[effective date start] >= minDate &&
'Table'[effective date start] <= maxDate &&
'Table'[is active]=1
)
 
However, with the same exact table and data, when changing it to filter using a single date where it is between the two effective date values, greater than the effective start date and less than the effective end date, the query takes an obnoxious amount of time to return my results. 20 seconds instead of an immediate response for the same 10 months of data. Yes, 20 seconds is way too long for any human to wait for anything, especially a visual refresh:
 
Test2 =
var maxDate = MAX('Date'[Calendar Date])
return CALCULATE(DISTINCTCOUNT('Table'[customer source key]),
'Table'[row effective end] >= maxDate &&
'Table'[row effective start] <= maxDate &&
'Table'[is active]=1
)
 
I am also noticing the query is not hardly utilizing the Vertipak engine, it's mostly in the formula engine. Is this expected? Should I expect a significantly slower response when attempting a date between two field values vs. a single column between two dates?
 
111.JPG
 
All suggestions that don't suggest using a calculated column or table are appreciated, because...this measure must be able to work with dynamic date selections, trend across calendar year(s) in visuals using heirarchies to weekly and daily detail, while comparing against prior year.
 
I can't use sumx, which cuts it from 20s to 8s, because the result isn't a distinct count. Also, 8s seconds is still way too long. These are the other versions I've tried:
 


Test2 =
var maxDate = MAX('Date'[Calendar Date])
return CALCULATE
(
SUMX
(
FILTER
(
'Table',
'Table'[is active] = 1 &&
'Table'[row effective end] >= maxDate &&
'Table'[row effective start] <= maxDate
),
1
)
)


Test2 =
var maxDate = MAX('Date'[Calendar Date])
var activeMembers = FILTER('Table','Table'[is active] = 1)
var activeMembers2 = FILTER(activeMembers,'Table'[row effective end] >= maxDate && 'Table'[row effective start] <= maxDate)
return CALCULATE
(
SUMX(activeMembers2, 1)
)


Test2 =
var maxDate = MAX('Date'[Calendar Date])
var activeMembers = FILTER('Table','Table'[is active] = 1)
var activeMembers2 = FILTER(activeMembers,'Table'[row effective end] >= maxDate && 'Table'[row effective start] <= maxDate)
return CALCULATE(DISTINCTCOUNT('Table'[customer source key]),
KEEPFILTERS(activeMembers2)
)


Test2 =
var maxDate = MAX('Date'[Calendar Date])
var activeMembers = FILTER('Table',
'Table'[is active] = 1 &&
'Table'[row effective end] >= maxDate &&
'Table'[row effective start] <= maxDate
)
return CALCULATE(DISTINCTCOUNT('Table'[customer source key]),
KEEPFILTERS(activeMembers)
)

 

Test2 =
var maxDate = MAX('Date'[Calendar Date])
var activeMembers = FILTER('Table','Table'[is active] = 1)
var activeMembers2 = FILTER(activeMembers,'Table'[row effective end] >= maxDate)
var activeMembers3 = FILTER(activeMembers2,'Table'[row effective start] <= maxDate)
return CALCULATE(DISTINCTCOUNT('Table'[customer source key]),
KEEPFILTERS(activeMembers3)
)


Test2 =
var maxDate = MAX('Date'[Calendar Date])
var activeMembers = FILTER('Table','Table'[is active] = 1)
var activeMembers2 = FILTER(activeMembers,'Table'[row effective end] >= maxDate)
var activeMembers3 = FILTER(activeMembers2,'Table'[row effective start] <= maxDate)
return CALCULATE(DISTINCTCOUNT('Table'[customer source key]),
activeMembers3
)

Test2 =
var maxDate = MAX('Date'[Calendar Date])
return CALCULATE(DISTINCTCOUNT('Table'[customer source key]),
KEEPFILTERS(
FILTER(
ALL('Table'[row effective end],
'Table'[row effective start],
'Table'[is active]),
'Table'[row effective end] >= maxDate &&
'Table'[row effective start] <= maxDate &&
'Table'[is active]=1
)
)
)


Test2 =
var maxDate = MAX('Date'[Calendar Date])
return CALCULATE(DISTINCTCOUNT('Table'[customer source key]),
KEEPFILTERS('Table'[row effective end] >= maxDate &&
'Table'[row effective start] <= maxDate &&
'Table'[is active]=1
)
)

 

Test2 =
var maxDate = MAX('Date'[Calendar Date])
return CALCULATE(DISTINCTCOUNT('Table'[customer source key]),
FIlter(ALL('Table'[row effective end]),'Table'[row effective end] >= maxDate),
FIlter(ALL('Table'[row effective start]),'Table'[row effective start] <= maxDate),
Filter(All('Table'[is active]),'Table'[is active]=1)
)


Test2 =
var maxDate = MAX('Date'[Calendar Date])
var activeMembers = FILTER('Table','Table'[is active] = 1)
return CALCULATE(DISTINCTCOUNT('Table'[customer source key]),
KEEPFILTERS(activeMembers),
'Table'[row effective end] >= maxDate &&
'Table'[row effective start] <= maxDate
'Table'[is active]=1
)


Test2 =
var maxDate = MAX('Date'[Calendar Date])
return CALCULATE(DISTINCTCOUNT('Table'[customer source key]),
KEEPFILTERS(
FILTER(
VALUES('Table'[row effective end]),
'Table'[row effective end] >= maxDate
)
),
KEEPFILTERS(
FILTER(
VALUES('Table'[row effective start]),
'Table'[row effective start] <= maxDate
)
),
KEEPFILTERS(
FILTER(
VALUES('Table'[is active]),
'Table'[is active]=1
)
)
)

 

Thank you!

 
 
 
 
 
 
2 REPLIES 2
PaulOlding
Super User
Super User

Can you share the real data (perhaps anonymised)?

I tried to recreate the issue by creating 100k rows of random data but I don't get the same query plan

PaulOlding_0-1626428884937.png

 

Thanks for your reply! Unfortunately I am prohibited from doing so, which I realize may make it difficult for others to help. At the moment I'm down the path of the suggestions here:

 

https://www.sqlbi.com/whitepapers/understanding-dax-query-plans/

 

Helpful resources

Announcements
Carousel_PBI_Wave1

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

BizApps LATAM 2023

Business Application LATAM Summit 2023

Join the biggest FREE Business Applications Event in LATAM this February.

Power Platform Bootcamp

Global Power Platform Bootcamp

In this bootcamp we will deep-dive into Microsoft’s Power Platform stack with hands-on sessions and labs, delivered to you by experts and community leaders.

Top Solution Authors
Top Kudoed Authors