Forum Discussion
refresh challenge for directquery and calculated table
Dear all,
I do have three different tables (one of them a DirectQuery - provided within the company where I can't change the Storage Mode as it is greyed out)
All tables have a seller column and I would like to combine the seller names into one slicer. There is no table with all seller names and it coulld be that names are listed only in 1, 2 or all tables/columns
To have this done, I was trying to calculate an additional table, like this:
seller =
DISTINCT(
union(
DISTINCT(Bike[seller]),
DISTINCT(RV[seller]),
DISTINCT(Car[seller)])
)
)
This is working as expected within PoBi Desktop, but not once published. During publishing the warning message below is already indicating this issue. "... calculated columns ... remote tables.... will result in refresh failures..."
Is there a more clever way to have one combined filter method (slicer) for more than one data source? VALUES was also not working ...
Much appreciated if someone could provide a direction.
Cheers Stefan
Hi,
Seems the issue is that your additional sellers from Excel don’t have details like city. This makes it impossible to combine them with the DirectQuery table in a calculated table and maintain proper relationships for visuals.
I see 3 possible options:SQL view in the source:
If possible, create a view that UNIONs all sellers and includes all necessary details (city, etc.)...This gives you a single, consistent table for slicers and visuals.
Mapping table for additional sellers
Keep DirectQuery as master...then add a small Excel table with the extra sellers and their details (city, etc.).
Use relationships + DAX measures to handle the extra sellers in your visuals
Separate slicer table
You could create a slicer table with all sellers, but visuals using city/details need measures to handle missing info for Excel only sellers..
Hope that helps !! Regards.
9 Replies
Hi StefD ,
May I ask if you have resolved this issue? Please let us know if you have any further issues, we are happy to help.
Thank you.
If you can’t change storage mode, the robust fix is to create a Seller dimension in the source.
- a view/table: DimSeller = UNION DISTINCT sellers from Bike/RV/Car
- then relate all facts to DimSeller
Hi StefD ,
Thank you for reaching out to Microsoft Community.This behavior is due to a Power BI Service limitation with DirectQuery.
Calculated tables and calculated columns are evaluated at model refresh time. When a calculation references a DirectQuery (remote) table, the Service cannot query that source during refresh, which leads to warnings and refresh failures. Because you can’t dynamically query DirectQuery sources at refresh, the recommended approach is to use a manual/disconnected dimension:
If you can access the sources, append the Bike, RV, and Car seller columns in Power Query, remove duplicates, and load the result as a DimSeller table. Use this DimSeller table in a slicer.
In your measures, apply the slicer selection to each fact table using TREATAS to pass the filter at query time. This pattern works reliably in the Service and supports mixed storage modes (Import + DirectQuery).
Hope this helps.Hi Stefan,
The DAX you wrote is correct and that’s why it works in Power BI Desktop.
However, the issue comes from a platform limitation when publishing. Calculated tables that UNION columns coming from DQ (remote) tables are not supported in the Service and will fail on refresh... that’s what the warning message is referring to.Because one of your tables is DirectQuery and locked in storage mode, a calculated table is unfortunately not a reliable solution here.
Better approaches would be:
Option 1. Build the Seller dimension in Power Query
Create references of each table, keep only the seller column, append them, and remove duplicates. This creates a physical table instead of a calculated one and publishes correctly.Option 2. Create a seller view in the data source
If you have database access, create a SQL view using UNION of the three seller columns and connect that as your slicer dimension table. This is the most robust solution.Hope this helps...
Best Regard,
Ricardowrt Option 1: I do not have access within Power Query (at least I'm not able to manage this right now) to the DirectQuery table which is looped in via our OneLake data hub. I could manage reference tables, appand... for the other two tables which are basically excel downloads - replaced/renewed regulary. As I'm doing this by my own, I have full control. I'm therefore not sure with the proposals to have the DIM_seller created in there or if I would need an additional 'dummy' table/file to create this. I'm not an expert at all, is there an easy way to have this done???
Thank you so for all the ideas provided so far.Kind regards Stefan
Hi, I See...
Try with these Steps:
Create references for the two Excel tables in Power Query.
Right-click the table → “Reference.” This creates a copy you can safely transform.
Keep only the seller column in each reference.
You can remove other columns by selecting them → “Remove Columns.”
Append the tables.
Append Queries → select the two references. This will combine the seller lists into one table.
Remove duplicates.
Select the seller column → Remove Duplicates. Now you have a unique list of sellers.
Load this as a new table in your model.
This table can act as your slicer/dimension table.
If you want, you could also create a small “dummy” Excel file that just lists the sellers, then refresh it whenever the Excel downloads are updated. This might make it easier to manage and keeps everything under your control.
This approach avoids the DirectQuery issue because the calculated table isn’t needed anymore.
Let me know. Hope it helps!
Regards,
Ricardo
Hi StefD ,
I just wanted to check if the issue has been resolved on your end, or if you require any further assistance. Please feel free to let us know, we’re happy to help!
Thank you.
