Power BI is turning 10, and we’re marking the occasion with a special community challenge. Use your creativity to tell a story, uncover trends, or highlight something unexpected.
Get startedJoin us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.
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/
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
Check out the June 2025 Power BI update to learn about new features.
User | Count |
---|---|
10 | |
9 | |
8 | |
6 | |
5 |
User | Count |
---|---|
20 | |
14 | |
10 | |
9 | |
6 |