Selecting between slowing changing dimension effective dates is very slow

[davidcray75]

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 key	customer source key	customer name	is active	effective date start	effective date end
1	aaa	customer 1	1	1/1/2019	1/31/2019
2	aaa	customer 1	0	2/1/2019	12/31/2099
3	bbb	customer 2	1	1/1/2020	12/31/2021
4	bbb	customer 2a	1	1/1/2021	12/31/2099
5	ccc	customer 3	1	1/1/2020	6/30/2021
6	ccc	customer 3a	0	7/1/2021	12/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?

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!

[PaulOlding]

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

[davidcray75]

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/