Forum Discussion
Power BI publisher for Excel - measures of SQL Server data source not usable as measure in excel
Hi BobHaze,
Yes, they are measures as we can see from this document. But they aren' the ones we can use in the Publisher for Excel which is another form of Analyze in Excel. You can see the details by clicking "Learn more" in the pop-up window. The measures we can use in the Publisher are those created by ourselves.
Why? Because the Pivot tables we use here is OLAP mode while there is another mode of Pivot tables of Excel named non-OLAP. You can see the difference here: differences-between-olap-and-non-olap-pivottables-in-excel.
How to create a measure? Please refer to: #cant-drag-anything-to-the-pivottable-values-area-no-measures.
Best Regards!
Dale
Microsoft EmployeeHi BobHaze,
Yes, they are measures as we can see from this document. But they aren' the ones we can use in the Publisher for Excel which is another form of Analyze in Excel. You can see the details by clicking "Learn more" in the pop-up window. The measures we can use in the Publisher are those created by ourselves.
Why? Because the Pivot tables we use here is OLAP mode while there is another mode of Pivot tables of Excel named non-OLAP. You can see the difference here: differences-between-olap-and-non-olap-pivottables-in-excel.
How to create a measure? Please refer to: #cant-drag-anything-to-the-pivottable-values-area-no-measures.
Best Regards!
Dale
I have accepted this as a solution but it seems to be a poor one.
What this "solution" infers is that in order to use the Power BI Publisher for Excel to do any kind of analysis against data models published in the Power BI service I have to create a duplicate measure for every measure that Power BI has already identified.
That seems short sighted at best.
- v-jiascu-msft
Hi BobHaze,
Actually, the last link #cant-drag-anything-to-the-pivottable-values-area-no-measures is the solution though it might not what we want.
>>>To address this issue, you have a few options:
1. Create measures in your data model in Power BI Desktop, then publish the data model to the Power BI service and access that published dataset from Excel.
2. Create measures in your data model from Excel PowerPivot.
3. If you imported data from an Excel workbook that had only tables (and no data model), then you can add the tables to the data model, then follow the steps in option 2, directly above, to create measures in your data model.
Once your measures are defined in the model in the Power BI service, you'll be able to use them in the Values area in Excel PivotTables.
Best Regards!
Dale
