How can I set up a scheduled refresh for tables that are DAX queries of a Power BI dataset?

[AlexisOlson]

In Power BI Desktop, I have defined some queries in the Power Query Editor that use a DAX query to build calculated tables from a dataset in the Service. A very simple one might look like this:

let
    DAXQuery ="
    EVALUATE
        SUMMARIZECOLUMNS (
            FundDealList[Alias],
            FundDealList[FundKey],
            FundDealList[FundOrder],
            TREATAS ( { ""ABC"", ""DEF"" }, FundDealList[Alias] ),
            ""Inception Date"", [DateInception]
        )
    ",
    Source = AnalysisServices.Database(
                 "powerbi://api.powerbi.com/v1.0/myorg/Work Space Name",
                 "Dataset_Name", [Query=DAXQuery, Implementation="2.0"]
             )
in
    Source

This works great in the Desktop editor and publishes to the Service just fine but I can't figure out how to set up the data gateway in the Service in order to be able to schedule an automated refresh within the Power BI Premium capacity.

I know that I can "live" connect to a Power BI dataset but this doesn't allow me to work with these simple pre-calculated tables that I can re-shape, augment, and connect to other data from somewhere else without modifying the original dataset. I've tried connecting to a Power BI dataset as a DirectQuery too but I get errors when I try to create calculated tables (like the one in my query above) in the local composite model if I include any measures in the definition.

How can keep my data up to date daily without having to republish from Power BI Desktop daily?

Suggestions? Advice?

[v-henryk-mstf]

Hi @AlexisOlson ,

Hope the solutions provided can help you.

link: Solved: Calculated table does not refresh after published ... - Microsoft Power BI Community

Best Regards,
Henry

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[AlexisOlson]

I'm not using DirectQuery at all. I'm importing a table from a DAX query of a Power BI dataset (as an Analysis Server). I only expect it to update when the model is refreshed but I haven't figured out how to set up a refresh in the Service via a data gateway.

[v-henryk-mstf]

Hi @AlexisOlson ,

Here is an official document that can guide you to use the gateway to connect to the analysis seriver data source to refresh the data. If there is still a problem, please even tell me the steps where the problem occurred, and I will help you.

Let me know immediately, looking forward to your reply.

Best Regards,
Henry

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[AlexisOlson · Accepted solution]

Thanks, @v-henryk-mstf. I ultimately resolved the situation by following the advice from here:

https://radacad.com/directquery-for-power-bi-dataset-how-does-it-work

In particular, I didn't need to add the dataset to the gateway, but I did need to configure it for OAuth2 under Data source credentials.

[v-henryk-mstf]

Hi @AlexisOlson ,

If your problem has been solved, you can mark your own answer as the correct answer. If the solution I provided can also help you, you can also mark it. Thank you very much.😃

Best Regards,
Henry

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[lbendlin]

Calculated tables are automatically refreshed as part of the dataset refresh. You don't need to do anything special.

[AlexisOlson]

I know that. My point is that I cannot figure out how to set up a dataset refresh in the Service in the first place for this situation. Basically, how can I get my data gateway to connect to a Power BI dataset as an Analysis Server?