Forum Discussion
direct query + excel refresh issue
hello all,
I built reports that have several datasources coming
- 1 one comes from a powerbi dataset using POWERQUERY
- others are from EXCEL (from sharepoint, but trying otherwise didn't solved the problem)
I first start by downloading the pbi dataset, then I import the excel file and get this message
translation : A DirectQuery connection is required. To make changes to your model (for example, renaming columns and adding data from multiple sources), you must switch to a DirectQuery connection. This requires adding a local template to your file and is a permanent change
and when I publish on pbi service
once again translation 😊 : Power BI Publishing This dataset contains calculated tables or columns that reference remote tables, resulting in refresh failures after publication. Do you really want to continue publishing?
and indeed ==> no auto update possible. I can manually refresh and republish pbix in service, but that's not what I call a smart way ...
is there a clever (and hopefuly easy) way to troubleshoot this ?
Hi juju63
Thanks for the reply from djurecicK2 and SaiTejaTalasila , please allow me to provide another insight:
According to your error code “This dataset contains calculated tables or calculated columns that reference remote tables”. This indicates that you have encountered a restriction on the calculated table or calculated column that references a remote table.
Did you connect to the dataset in direct query mode in Power BI Desktop and then create the calculated table?
If so, could you please check to see if a calculated table was created in it? Calculation tables are an import mode that can cause refresh failures.
Also you can check if you meet another limitation in this:
Solved: Composite Models using Power BI datasets, Calculat... - Microsoft Fabric Community
Use composite models in Power BI Desktop - Power BI | Microsoft Learn
Best Regards,
Yulia XuIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
6 Replies
Hi juju63 ,
You can create a composite model (mixed) and you can build report on top of it.I hope it will work.
Thanks,
Sai Teja
Hello Sai Teja,
I misspelled instead of POWERQUERY, I wanted to say DIRECTQUERY. see the screenshot of my connections
I think it's already a composite model ...
Hi juju63 ,
This is normal behavior. When you create a report that uses a published Power BI Semantic Model as the data source, the storage mode will be live connection. When you add excel, you can no longer use live connection mode and must use Direct Query.
From Power BI Desktop, you can click Make changes to this model in the bottom right
Here is additional information:
https://learn.microsoft.com/en-us/power-bi/connect-data/service-live-connect-dq-datasets
Hi juju63
Thanks for the reply from djurecicK2 and SaiTejaTalasila , please allow me to provide another insight:
According to your error code “This dataset contains calculated tables or calculated columns that reference remote tables”. This indicates that you have encountered a restriction on the calculated table or calculated column that references a remote table.
Did you connect to the dataset in direct query mode in Power BI Desktop and then create the calculated table?
If so, could you please check to see if a calculated table was created in it? Calculation tables are an import mode that can cause refresh failures.
Also you can check if you meet another limitation in this:
Solved: Composite Models using Power BI datasets, Calculat... - Microsoft Fabric Community
Use composite models in Power BI Desktop - Power BI | Microsoft Learn
Best Regards,
Yulia XuIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hello v-xuxinyi-msft
so according to what I saw in the hyperlink, there's no solution on this topic ...
how letdown it is ...
