Thanks a lot. Can you link data to Excel's data model directly so the end user can use dax on local machine without using power query because of much data?
@@AccessAnalytic Thanks. Actually.... I played with it and got it connected directly into data model form the BI table. I don't have power query at all but have a power bi connection to powerpivot. Is there any resource here to show screenshot?
So I’m not quite following. Are you connecting to a Power BI data model that lives in PowerBI.com or are you connecting to a Power Pivot data model that lives in a local Excel file.
@@AccessAnalytic Here is the protocol: 1. In the PowerBI Destop: Enable that report can export underlying data (CURRNET FILE > Report Setting, Export Data) Make a simple table with a Sum measure (two columns - date and measure (Sum of Values)) Upload Dataset to the PowerBI Service 2. In PowerBI Service: Click on table and export to Excel "summarised data with leave connection" 3. In Excel: Find this connection (under Data & Connections) and copy connection string to any text editor Edit this string to remove statement 4. In this or any other Excel file: Open Data Model in Excel Select Get External Data > From Other Sources > Others (OLEDB/ODBC) Paste Connection String and Test Connection In the MDX field Enter DAX statement starting with EVALUATE for the dataset table. If table name is tb1, the statement that brings all the data to the Data Model will be EVALUATE tb1. Alternatively one can do other filters and functions as FILTER, CALCULATETABLE, SELECTCOLUMNS, etc Confirm with Finish. Now refreshable data is in the Data Model and can be used for reports in Excel. Please let me know if it works for you.
Loved the live connected/refreshable table. Never knew that was possible.
Cheers Emily!
Brilliant. Thanks Wyn!
You’re welcome 😀
This is valuable information. Thanks Wyn, for providing all these options!
Cheers Iván.
Excellent! Thank you!
You’re welcome.
Great
🎉
Thanks a lot. Can you link data to Excel's data model directly so the end user can use dax on local machine without using power query because of much data?
You can’t connect to a data model in a separate Excel file, but can create the table connection inside the same file as the model
@@AccessAnalytic Thanks. Actually.... I played with it and got it connected directly into data model form the BI table. I don't have power query at all but have a power bi connection to powerpivot. Is there any resource here to show screenshot?
So I’m not quite following. Are you connecting to a Power BI data model that lives in PowerBI.com or are you connecting to a Power Pivot data model that lives in a local Excel file.
@@AccessAnalytic Here is the protocol:
1. In the PowerBI Destop:
Enable that report can export underlying data (CURRNET FILE > Report Setting, Export Data)
Make a simple table with a Sum measure (two columns - date and measure (Sum of Values))
Upload Dataset to the PowerBI Service
2. In PowerBI Service:
Click on table and export to Excel "summarised data with leave connection"
3. In Excel:
Find this connection (under Data & Connections) and copy connection string to any text editor
Edit this string to remove statement
4. In this or any other Excel file:
Open Data Model in Excel
Select Get External Data > From Other Sources > Others (OLEDB/ODBC)
Paste Connection String and Test Connection
In the MDX field Enter DAX statement starting with EVALUATE for the dataset table. If table name is tb1, the statement that brings all the data to the Data Model will be EVALUATE tb1. Alternatively one can do other filters and functions as FILTER, CALCULATETABLE, SELECTCOLUMNS, etc
Confirm with Finish. Now refreshable data is in the Data Model and can be used for reports in Excel.
Please let me know if it works for you.