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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
aadames
Regular Visitor

the field you are moving cannot be placed in that area of the report

When opening the ODC file in Excel, downloaded using the Analyze in Excel option for the dataset in Power BI Service, the measure is not available for the value area in the pivot table. The following error message appears: The field you area moving cannot be placed in that area of the report.

 

Curiously, if I connect from Excel to SSAS Cube the measure is available for the pivot table.

 

Any idea? I think this could be a bug.

 

Thanks,

Armando

1 ACCEPTED SOLUTION

Hi @aadames,

I can reproduce your issue when connecting to SSAS cube from Power BI Desktop via “Import data” option, in this scenario, when you import measures of SSAS cube into Power BI Desktop, Desktop treats the imported model as a normal table and recognizes the measures as normal fields in this table, this behavior is by design. Thus, once you open ODC file of this dataset, you are not able to drag the measure into Values area, and you define a new measure based on original measure in order to work around this issue.


However, when connecting to SSAS cube from Power BI Desktop via “Connect live” option, measures are displayed as follows. And when you open ODC file of the dataset, you are able to drag measures into Values area.
1.PNG2.PNG


Thanks,
Lydia Zhang

Community Support Team _ Lydia Zhang
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

10 REPLIES 10
aadames
Regular Visitor

When opening the ODC file in Excel, downloaded using the Analyze in Excel option for the dataset in Power BI Service, the measure is not available for the value area in the pivot table. The following error message appears: The field you area moving cannot be placed in that area of the report.

 

Curiously, if I connect from Excel to SSAS Cube the measure is available for the pivot table.

 

Any idea? I think this could be a bug.

 

Thanks,

Armando

Hi, ankitpatira. Thanks for your response. My measure is well defined and I used it on BI visualizations. The problem is when I choose "Analyze in Excel" option, for the dataset in power BI service.analyze in excel.png

This option create an ODC file which is opened with Excel in Pivot Table. There, the measure appears like a common dimension.

Power Bi Measure:

power bi measure.jpg

Excel Pivot Table:

Excel Pivot.jpg

 

Try it, if you have a dataset in power bi service. Any feedback will be appreciated.

 

Thanks.

Hi @aadames,

Firstly, as stated in this article, in a PivotTable, measure can be placed in the Values area.

Secondly, from your description, you are not using measure, in your scenario, ∑Admitted is a numeric filed that can be aggregated but not a measure.  In Power BI, measures are shown with a calculator symbol, below is an example. You can check this article to better understand measures.
1.PNG

Thirdly, as other post, when you open ODC file, you will see that measures will be listed separate to all the fields, and you are able to drag them to Values area. I test the scenario and everything works well as below.
2.PNG



Thanks,
Lydia Zhang

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

Thank you! finally found an answer I can understand.

Created "measure" for the field I'm trying to calculate and worked!

Hi

 

I have the same issue.  I have a SQL Stored Procedure designed from a Microsoft Dynamics NAV 2017 database.  The stored procedure contains about 34 values direct from the database or a calculation in the stored procedure.  Power BI dataset is linked to the Stored Procedure.  All the visualisations and reports are displaying the data correctly.  When I link Excel directly to the stored procedure the pivot table display the data correctly and the totals are correct.  However when I select a Power BI report to Analyse in Excel then I cannot use the value fields as numbers ot total etc. 

 

I find it a duplicaiton and time consuming to create a measure for every number field in the stored procedure.  Is there any plan in the future to fix this in the future?

 

Regards

Robin

Hi

I have the same issue from a Microsoft Dynamics NAV 2917 database.  I have a SQL Stored procedure with approximately 34 numeric values either as fields or calculated in the stored procedure.  Power BI is connected to this stored procedure and the various report vizualisations display correctly and sum the vlaues correctly.  If I connect directly from Excel to the Stored Procecedure my Pivot tables work correctly, by totalling the values correctly and I can drag to the values design block. 

 

The issue is immediately when I try Analyze with Excel from Power BI.  I find it very frustrating that I now have to create a measure for every value in my dataset from the SQL Stored Procedure.  This seems to be a big duplication and waste of time.

 

Is this a bug? and will it be resolved to avoid a measure per value in future versions?

 

Robin

Hi Lydia, thanks for your feedback. I created another measure in power Bi dataset and it worked fine. But, let me explain this scenario. My dataset was imported from SSAS Cube, where "admitted" is a measure there. Once the data is uploaded, the measure "admitted" appears like a numeric (∑) field. I did it again, defined a new measure (measure = sum([admitted]) and everything worked fine. When the ODC file was saved using "Analyze in Excel" option for the dataset, the new power BI measure was available for pivot table.

 

Why I need to duplicate the measure that is defined in the SSAS Cube? I don't know. But this is a work around.

 

Thanks, very much.

 

Armando

Hi @aadames,

I can reproduce your issue when connecting to SSAS cube from Power BI Desktop via “Import data” option, in this scenario, when you import measures of SSAS cube into Power BI Desktop, Desktop treats the imported model as a normal table and recognizes the measures as normal fields in this table, this behavior is by design. Thus, once you open ODC file of this dataset, you are not able to drag the measure into Values area, and you define a new measure based on original measure in order to work around this issue.


However, when connecting to SSAS cube from Power BI Desktop via “Connect live” option, measures are displayed as follows. And when you open ODC file of the dataset, you are able to drag measures into Values area.
1.PNG2.PNG


Thanks,
Lydia Zhang

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

Mmmm... this is just another CLASSIC BUG from the PowerBI team. Technically they are saying that the only "measures" that exist are those specifically created as such in PowerBI (through some DAX formula for example); but if you already have REAL measures in your orginal dataset, then... those are not measures, but dimensions (user's datasets only have dimensions basically).

Please, this is NON-SENSE!!! Tableau allows the user to define whether your data is a measure/dimension since the beginning of times... you really need to catch-up to common sense behaviour, and quickly.

Cheers!

ankitpatira
Community Champion
Community Champion

@aadames When you open ODC file do you see it listed above all the fields (measures will be listed seperate to all the fields). If you do then you should be able to drop them to Values section. If you don't see then in power bi desktop you have to specifically create measures.

 

Capture.png

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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

Top Solution Authors
Top Kudoed Authors