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.
Good 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.