Forum Discussion
refresh challenge for directquery and calculated table
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.
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,
Ricardo
wrt 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,
RicardoGood morning,
thank you for the additional hints.
Steps 1 -5 are clear and this is working. However I need to have the sellers from the DirectQuery as well. In addition, there are additional information within the DirectQuery list which I'm trying to show in a report table. This isn't wokring either despite several approaches with the relationship settings e.g. many-to-may and filter in both directions between the tables directly or between the calculated table. Let me try to illustrate to issue...
The DirectQuery table is like a master table, with the challange that not all sellers are listed... Therefore all sellers form the master AND all additional sellers from the other tables shall be within the calculted table to enable one filter/slicer to generate a report with a visual and table where all sellers are listed together with additonal details, like city, and other calculations...
Thank you in advance. Cheers Stefan
DirectQuery calc. table seller city seller city Liam London Liam London Stuart Paris Stuart Paris Stefan 😊 Berlin --> Stefan Berlin Excel1 Jules ""(empty) Jules Nicola ""(empty) Stuart Excel2 Nicola 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.