topic What is the most efficient way to retrieve rows with valid from to dates? in DAX Commands and Tips

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

speedramps — Sat, 07 Mar 2020 14:37:28 GMT

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)

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

Anonymous — Sat, 07 Mar 2020 15:42:40 GMT

I think you already got best solution.

This will work absolutely fine.

Thanks
Pravin

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

Greg_Deckler — Sat, 07 Mar 2020 16:56:44 GMT

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

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

Anonymous — Sat, 07 Mar 2020 21:30:54 GMT

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

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

Greg_Deckler — Sat, 07 Mar 2020 22:01:25 GMT

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?

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

Greg_Deckler — Sat, 07 Mar 2020 22:47:07 GMT

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!

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.