Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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
Solution Sage
Solution Sage

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors