I have a report with a Fact table and several dimensions, all of which are in DirectQuery mode. So the report Storage Mode is DirectQuery. However, as soon as I add a new table using DAX GENERATESERIES, that new table is created as Import mode, and I can't change it. And that forces the Storage Mode to Mixed.
That's okay, up to a point. But when I publish it to Service, Mixed mode means I have to use Scheduled Refresh to update the "Import" tables (even though they are not imported from anywhere), and I can only do that 8 times a day unless I purchase expensive dedicated capacity.
And it does not allow me to use Scheduled Cache Refresh for the dashboards, as it would when everything was Direct Query. So dashboards aren't kept fresh, as they are if the mode is Direct Query.
Is there any way to set the mode of a DAX table to Direct Query, or ignore DAX tables in determining the report mode, or force the report to be Direct Query when DAX tables are present?
I've seen other posts and blogs on this subject, but they either a) pre-date composite models, or b) don't address the refresh aspects of Mixed mode.
and measures like this that only show a value if it's within the ranges of interest:
KPI Test Count (FailPct FailDev) =
var LowPctVal = [Min Fail Pct Value] var HighPctVal = [Max Fail Pct Value] var FailPct = [KPI Fail Percent]
var LowDevVal = [Min Fail Dev Value] var HighDevVal = [Max Fail Dev Value] var DevPct = [KPI Fail Deviation]
IF( FailPct>=LowPctVal && FailPct<=HighPctVal && DevPct>=LowDevVal && DevPct<=HighDevVal,[Total Test Count])
This way, the user can restrict the display to, say, just those rows between 3% and 5%, or just those rows between 5% and 10% deviation from the expected value. This works just great.
The problem I have is that the DAX tables force the model to Mixed mode. So Power BI Service does not allow me to set a schedule for refreshing the tiles, and they are not refreshed automatically. I must schedule a full dataset "refresh" just to get the tiles updated. And I can only do that within the limits of dataset refreshes - 8 times a day, in our case.
That's just plain silly. No data is ever going to be imported to the two DAX tables - they always contain 101 rows from 0.00 to 1.00 in increments of 0.01. This is generated by DAX, and never needs to be refreshed. But it breaks the refresh of my dashboard tiles.
Is there any way to get around this? It seems I'm forced to either:
Not allow the user to focus the report as desired, or
The tiles that display the Fact table data (within the selected ranges) will be pretty stale, and I'll schedule the dataset to be "refreshed" once every two hours during the workday and once or twice overnight.
Neither of these is ideal. If there's no other choice, I'll go with option 2 and inform the users they have to go to the report and wait for all of the queries to run in order to see anything more recent than a few hours old.
I'm looking for alternatives. Does anyone have any suggestions?
Hi, @sturlaws , thanks for your reply. My data source is also SQL Server.
I hadn't considered querying the numbers from SQL Server - seems like a lot of unnecessary overhead to go out and get such constants across the Enterprise Gateway, etc. But I'll give it a try and check it out with SQL Profiler to see what the impact is.