Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount.
Register nowThe Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.
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:
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!
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
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/
User | Count |
---|---|
20 | |
18 | |
17 | |
11 | |
7 |
User | Count |
---|---|
29 | |
28 | |
13 | |
12 | |
12 |