Add Dataset to Excel data model

[jdriscoll]

Hi,

I tried using the "Analyze in Excel" feature on a dataset I have in app.powerbi.com. Doing so creates an Excel file, which includes all the columns from all the tables in the dataset, but it doesn't include any measures from the dataset. When I try to create measures by going to the Power Pivot ribbon and selecting "Manage", it shows that there is no data model. I've read through a bunch of the Microsoft documentation, and it says you can add the dataset to your data model in Excel, but I'm not clear on how that's achieved. Any help or pointers to another article would be much appreciated. I should also point out that I created the dataset in the Power BI service, so I should have full permissions for it (at least I would think).

[jdriscoll]

Hi,

In this link, "Build refreshable Excel reports with Power BI data", there's "Considerations and Limitations" section towards the bottom that says the following:

"Excel PivotTables don't support drag-and-drop aggregation of numeric fields. Your dataset in Power BI must have pre-defined measures. Read about creating measures."

In reading all the Microsoft articles it seems like there is a way to either import measures already creted in Power BI or to import the dataset into the data model in Excel to recreate the measures in Excel.

[bcdobbs]

Hi,

What have you seen that suggest you can add the dataset to the excel data model? I'm happy to be wrong but when you connect to a power bi dataset the engine and data are all cloud based and excel just queries it. The issue with excel is it doesn't support implicit measures on columns.

eg when you drag sales column into a power bi matrix it assumes you want to do SUM(Table[Sales]).

Excel for historical reasons (it's still based on MDX) can only deal with explicit measures. Eg you've physically created the measure in the model.