Forum Discussion
Power BI publisher for Excel - measures of SQL Server data source not usable as measure in excel
Running into a problem with automatically identified measures in the pbix.
Here’s the sequence:
- PBI desktop connect to SQL Server via Direct Query or Import.
- Note which columns are automatically identified as measures with Ʃ.
- Save pbix & publish to a workspace
- Open Excel with Power BI Publisher add-in installed.
- Click Power BI ribbon tab.
- Click to ‘Connect to Data’
- Choose the workspace
- Click ‘Dataset’
- Select dataset name from drop-list.
- Most likely you will receive a pop-up dialog which says no measures were identified.
If you click past it… - A pivotTable is created and the Fields well opens on the right.
- Try to drag any of the columns identified as measures in step 1 to the ‘Values’ area.
- A message dialog will appear telling you that the field cannot be placed in that area of the report.
In order for the Publisher for Excel to recognize a column as a measure it has to be created as a new measure or a Quick Measure in the pbix before being published.
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
3 Replies
v-jiascu-msftMicrosoft Employee
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
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
