Forum Discussion
Keeping up with (static) Dimension Table when FactTable updates | How do you do it? Best practices?
Direct Query makes things more complicated but it should still be possible to create a dimension table either as a DAX calculated table--something like SUMMARIZE ( Fact, Fact[Customer] )--or by creating a similar new query in the query editor that links to the same source as the direct query. In the first case, you could do some kind of union or join with your spreadsheet table to get the DimCustomer table to use for the model. Same for the latter; you'd append or merge the two sources together to get a full dimension table.
With any method, you'll need to do a model refresh to update static data additions since you can't DirectQuery a spreadsheet (last I heard anyway).
Thanks for the response. The only reason I'm using Direct Query is because I am not the owner of the dataset - The data is being pulled in via API calls in Python (which was done by my colleague) - So the only way for me to access the updated data, I had to connect to Power BI Service Dataset, which is by default, direct query.
Just as an FYI, you can connect to a Power BI Service Dataset using import.
Here's an example:
let
Workspace = "Workspace_Name"
Dataset = "Dataset_Name"
DAXQuery = "EVALUATE SUMMARIZECOLUMNS ( DimClient[ClientName], DimClient[ClientID] )"
Source = AnalysisServices.Database(Workspace, Dataset, [Query=DAXQuery, Implementation="2.0"])
in
Source