Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
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
Solved! Go to 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.
Thanks,
Lydia Zhang
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.
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:
Excel Pivot Table:
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.
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.
Thanks,
Lydia Zhang
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.
Thanks,
Lydia Zhang
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!
@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.
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
39 | |
26 | |
21 | |
19 | |
10 |
User | Count |
---|---|
41 | |
36 | |
34 | |
20 | |
14 |