Forum Discussion

StefD's avatar
StefD
Frequent Visitor
6 months ago
Solved

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...
Need Help
  • RicardoTraNa's avatar
    RicardoTraNa
    6 months ago

    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.



v-echaithra's avatar
v-echaithra
Community Support
6 months ago

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.