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

[speedramps]

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)

[Anonymous]

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

[Greg_Deckler]

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?

[Greg_Deckler]

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 Facts

Retrospective 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. 

[Greg_Deckler]

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

[Anonymous]

I think you already got best solution.

This will work absolutely fine.

Thanks
Pravin