New DAX table forces Mixed Mode

[SamTrexler]

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.

Thanks in advance for your help.

[v-lili6-msft]

hi, @SamTrexler 

Create calcuate table in directquery will lead the Storage mode to Mixed Mode.

https://docs.microsoft.com/en-us/power-bi/desktop-composite-models#calculated-tables

and if a similar post could help you:

https://community.powerbi.com/t5/Power-Query/Schedule-Refreshment-of-a-composite-model/td-p/661085

Best Regards,

Lin

[SamTrexler]

@v-lili6-msft , thanks for the reply. Those are interesting, but neither addresses my issue.

The Fact table and ten dimensions are Direct Query, and this works great. The data is always queried when the user wants it, and dashboard tiles are refreshed once an hour. Perfect.

However, the user doesn't always want to see hundreds of rows, they would like to focus the report down to just those few rows of interest. So I've created two generated tables:

ZZZ Fail Percentage = GENERATESERIES(0.00, 1.01, 0.01)
ZZZ Fail Deviation = GENERATESERIES(0.00,1.01,0.01)

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]
return
    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:

1. Not allow the user to focus the report as desired, or
2. 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?

Thanks in advance.

[sturlaws]

Hi @SamTrexler,

what is your data source?

I just tried to create a similar case as yours, with direct query to a SQL Server. Instead of using DAX and GENERATESERIES, I constructed the necessary values in a SQL query. It seems to be working.

cheers,

s

[SamTrexler]

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.

Thanks for the suggestion.