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).
Ideally you should include a field for team manager at the source. If the Employee works exclusively for each team, you could create measures to group by team manager. If you can actually create tables in Direct Query mode (using DAX or PQ) you could of course create a dimension table including Unique Employees and their manager.
Just out of curiosity, why are you using Direct Query?
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 SourceJust a tought... If each employee is assigned to one team, you could create the dimenaion table for employee-manager in Excel instead of the client sheet. You would only then need to update the employee sheet to cover employee/team changes. Makes sense?