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

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
speedramps
Super User
Super User

What is the most efficient way to retrieve rows with valid from to dates?

neeMy fact table has a valid from and to date for each row.

The user needs to select a date from a dropdown list of dates and then see the valid facts retrospectively at that point of time.

The fact table has millions of rows so I want to use the most efficient way as possible.

Is this DAX measure the most efficient way to do it?

I am concerned FILTER is an iterator and tests row by row  

Many thanks for helping


Retrospective Facts =

--- get selected user date

VAR userdate  = SELECTEDVALUE('List of dates'[Date])

 

--- create a subset table with rows that were valid at the point of time

VAR facts_valid_at_point_in_time =

FILTER(
FactTable,

userdate >= FactTable[Valid from] &&

userdate <= FactTable[Valid to]
)

 

RETURN

--- counts rows in the subset

COUNTROWS(facts_valid_at_point_in_time)

5 REPLIES 5
Anonymous
Not applicable

Of course, this isn't THE BEST solution from the point of view of performance. Doing a FILTER on a fact table should always be avoided. Filter is OK on dimensions.

The fastest solution is always based on RELATIONSHIPS. You should have a table that repeats any fact row (or just the fact id - if you don't have it, you'll need to create it) for all the dates between ValidFrom and ValidTo. There'll be a date column in the new table for the dates and your table with dates (the one you use for slicing) would be connected to this new table. Then, if you select a date from the date table, it'll auto-filter all the facts where the condition you have up there is true. This IS the fastest way to do this. It'll be at least one magnitude faster than the one based on FILTER.

# Retro Facts = distinctcount( FactTable2[FactId] )

Best
D

If I am following @Anonymous you want to generate a row for every fact row between the two dates? So if you have a table of 20M rows and the average date span is 100 days, then that would be 2 billion rows, or 2 orders of magnitude more rows than the original data set size. So you are saying that increasing the data set size by an order of magnitude of 2 and using relationships will net an order of magnitude increase in performance?



Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
DAX For Humans

DAX is easy, CALCULATE makes DAX hard...

OK @Anonymous , @Anonymous and @speedramps , I find matters of performance pretty interesting so I ran some tests. Here is what I did. 

 

I created these 2 tables:

 

Calendar = CALENDAR(DATE(2020,1,1),DATE(2020,12,31))

Table = 
    ADDCOLUMNS(
        GENERATESERIES(1,1000000,1),
        "From",DATE(2020,1,RANDBETWEEN(1,28)),
        "To",DATE(2020,12,RANDBETWEEN(1,28))
    )

 

The data model size is 11MB.

 

I then created this table based upon what I think @Anonymous was saying (I could be wrong!)

 

Table 2 = 
    GENERATE(
        GENERATESERIES(1,1000000,1),
        CALENDAR(DATE(2020,1,15),DATE(2020,12,15))
    )

 

Now my data model grew to 326MB, or almost 30 times larger in disk/memory size.

 

I created a relationship between Calendar and Table 2.

 

So now I created these two measures:

 

Retrospective Facts = 
VAR userdate  = SELECTEDVALUE('Calendar'[Date])
VAR facts_valid_at_point_in_time =
FILTER(
'Table',
userdate >= 'Table'[From] &&
userdate <= 'Table'[To]
)
RETURN
COUNTROWS(facts_valid_at_point_in_time)


Retrospective Facts 2 = COUNTROWS(RELATEDTABLE('Table 2'))

 

 

I put Calendar[Date] into a list slicer. I created a card visual and  put Retrospective Facts measure into the Card visual. I then duplicated this page and on the duplicate page I put replaced Retrospective Facts in the Card visual with Retrospective Facts 2.

 

I then turned on Performance Analyzer and analyzed the performance on each page. Screen shots are posted below. 

 

I get an average of 177 for Retrospective Facts and 179 for Restrospective Facts 2. So, I agree with @Anonymous that the relationship route is way faster than using filtering (if you have the same number of fact records). However, I'm not certain that the trade-offs are always worth it in terms of data model size and the increase in the number of fact records. 

 

But, great little experiment to run! 

 

 

Retrospective FactsRetrospective Facts

 

 

 

Retrospective Facts 2Retrospective Facts 2

 

I would upload my PBIX but the file size is too large. I would also upload my JSON performance export files but this site does not allow json files to be uploaded apparently. 

 



Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
DAX For Humans

DAX is easy, CALCULATE makes DAX hard...
Greg_Deckler
Community Champion
Community Champion

I do not see any obvious way to improve what you have there.



Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
DAX For Humans

DAX is easy, CALCULATE makes DAX hard...
Anonymous
Not applicable

I think you already got best solution.

This will work absolutely fine.

Thanks
Pravin

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

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

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.