Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
BobHaze
Frequent Visitor

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:

  1. PBI desktop connect to SQL Server via Direct Query or Import.
    1. Note which columns are automatically identified as measures with Ʃ.
  2. Save pbix & publish to a workspace
  3. Open Excel with Power BI Publisher add-in installed.
  4. Click Power BI ribbon tab.
  5. Click to ‘Connect to Data’
    1. Choose the workspace
    2. Click ‘Dataset’
    3. Select dataset name from drop-list.
    4. Most likely you will receive a pop-up dialog which says no measures were identified.
      If you click past it…
  6. A pivotTable is created and the Fields well opens on the right.
  7. Try to drag any of the columns identified as measures in step 1 to the ‘Values’ area.
    1. 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.

1 ACCEPTED SOLUTION
v-jiascu-msft
Employee
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.Power BI publisher for Excel - measures of SQL Server data source not usable as measure in excel .jpg

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Best Regards!

Dale

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

3 REPLIES 3
v-jiascu-msft
Employee
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.Power BI publisher for Excel - measures of SQL Server data source not usable as measure in excel .jpg

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Best Regards!

Dale

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

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.

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

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.