Best way to combine 2 separate fact tables, one is DirectQuery the other is import

[Don-Bot]

I have inherited a semantic model that was created as an import model with incremental refresh.  This model doesn't follow start schema very well as it's fact table is a large table with most dimensions/dates within it .  

I am now in need of separating this fact table out sot hat the first 10 days of data are DirectQuery and then the remaining 20 days of data are import.  However, I am unsure how to do this as i can't simply create 2 fact tables and have them share dimensions and then create measures that follow that.  

I tried doing an aggregation but when run it is only pulling in the older data that is using as the detail table but not the most recent information.

My ideal scenario is the live data would come through in a table with/without the older data depending on how the users filter.  Also, it would be nice if the older data was import and the recent/live data was direct query.   But I need the recent data to be DirectQuery.

Any thoughts?

The data looks something like this

[lbendlin · Accepted solution]

The general Advanced Incremental Refresh setup only has "Today" as a Direct Query partition, everything else is import mode.

https://learn.microsoft.com/en-us/power-bi/connect-data/incremental-refresh-xmla

So if you want to do "the last ten days"  in Direct Query you will have to do some manual partition gymnastics.

[Don-Bot]

@lbendlin thank you for that suggestion.  It looks like that got me a lot closer but I am running into one error. 

I setup the partitions like this

Each partition is almost exactly alike except they each point to a different view.  One view is "Older" which has all tickets older than 10 days, the other view is "recent" and has all tickets from now until 10 days.  Create Date is the date filter for the views.

and for the most part it seems to work.  However, I found if I pull in "Create DateTime" as a slicer from the ticket fact table (yes I know bad design) that the visuals start messing up if I filter by it. The views that error are just a # of ticket view and another dimension from the fact table.  # of tickets is this dax:

                var variable = COUNTROWS(Ticket)
 
             return variable

and the error is

Error fetching data for this visual

DirectQuery error: The DAX query failed to generate a DirectQuery query because it exceeded the maximum number of allowed parameters/literals, '2100'. This usually occurs when a DistinctCount measure is computed for group-by columns which are indirectly related to the DistinctCount column. This can be due to the presence of bi-directional cross-filtering.

Please try again later or contact support. If you contact support, please provide these details.

I assume it has something to do with the dimension filter being on the fact table and getting confused when I try to slice by it.  Not sure if there's a way around this besides maybe moving the filter outside of the fact table like it should be.

[lbendlin]

yes, move that filter to the calendar table where it belongs.  How big is your calendar? Remember it should cover the fact data plus whatever time intelligence you want to do beyond the facts, but not more.

[Don-Bot]

Hi @IB , just wanted to put this here in case someone else came along.  But another thing that helped was switching all the dimensions to dual instead of just directquery.