cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Yggdrasill
Resolver III
Resolver III

SAP BW MDX Query and Measure properties in Power Query

When I query this MDX statement 

with member [Measures].[Revenue] AS [Measures].[TECHNICALNAME1]
     member [Measures].[Total Cost] AS [Measures].[TECHNICALNAME2]     

select {[Measures].[Revenue],
        [Measures].[Total Cost]} on columns,
non empty
{[YEARMONTH].[LEVEL01].Members} on rows
from [CUBEtechnicalname]
where [YEARMONTH].[2018/09]: [YEARMONTH].[2018/10]


on SAP BW Cube, I get this result

 

Year/Month | Revenue| UNIT_OF_MEASURE | Total Cost | UNIT_OF_MEASURE

2018/0912346798EUR14451EUR
2018/10132456789EUR65684132EUR

 

Why on earth are do I get extra columns with Unit of measure, and how do I get rid of it in the query statement (not Power query editor)

6 REPLIES 6
mpi_gov_vn
Frequent Visitor

I know this is an old post, but I also have the exact issue. Were you guys able to handle it?

pwagner23AZ
New Member

The "Extra" UM is not extra from SAP perspective, money comes with the currency as the UM.  For example, if you add Canadian $ and US $ together you do not get a real amount.     

JonArgandona
Helper I
Helper I

@Yggdrasill  I know this is an old post, but I'm facing the same issue. Were you able to resolve it?

v-jiascu-msft
Microsoft
Microsoft

Hi @Yggdrasill,

 

I have consulted an expert. The answer is the [YEARMONTH].[LEVEL01].Members is the source of the unwanted columns. Please check if it's a user hierarchy and what it has.

 

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.

Thanks for taking the time Dale.
However if I take out the [YEARMONTH] mentioned dimension I still get the unwanted columns with UNIT_OF_MEASURE after each and every "Key figure" (measure) which is defined in SAP BW.
I don't think the dimension has anything to do with the unwanted UOM columns I get for every measure I drag in the query. I think this has something to do with the measure property rather than the dimension property.
Question is, what do I have to do with the measure, to only get the value and NOT the UOM value.

Here's where I'm at now

WITH Member [Measures].[Revenue] AS [Measures].[00O2TQDIHUNPIH5VRVH89Z3HR] 
Member [Measures].[Cost] AS [Measures].[00O2TQDIHUNPIH5VRVH8A1WMN]
SELECT NON EMPTY
{ [Measures].[Revenue],
[Measures].[Cost], } ON COLUMNS,
NON EMPTY
{ {[ZPERIOD].[LEVEL01].Members} *
{[0COMP_CODE].[LEVEL01].Members} *
{FILTER([0PROFIT_CTR].[LEVEL01].Members, [Measures].[Revenue] <> 0 ) } *
{[0PAYER].[LEVEL01].Members} *
{[0SHIP_TO].[LEVEL01].Members} *
{[0SOLD_TO].[LEVEL01].Members} *
{[ZLOADCTYF].[LEVEL01].Members} *
{[ZDISCTYF].[LEVEL01].Members} *
{[ZLOADPRTF].[LEVEL01].Members} *
{[ZDISPORTF].[LEVEL01].Members} *
{[ZTLCLD__ZTLCLDG].[LEVEL01].Members} *
{[ZTLCLD__ZTLCLDC].[LEVEL01].Members} *
{[ZLOGSETY].[LEVEL01].Members} }
DIMENSION PROPERTIES
[techname].[tech name for key],
[techname2].[tech name2 for key] ...
ON ROWS
FROM [cube tech name]
WHERE [ZPERIOD].[2018/09]: [ZPERIOD].[2018/10]

 In return I still get that extra column 

[Measures].[Revenue][Measures].[Revenue].UNIT_OF_MEASURE[Measures].[Cost][Measures].[Cost].UNIT_OF_MEASURE
99999EUR9898EUR

Hi @Yggdrasill,

 

I would suggest you go to Forums/sqlserver/en-US/home?forum=sqlanalysisservices for professional support. 

 

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
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

Get Ready for Power BI Dev Camp

Microsoft named a Leader in The Forrester Wave

Microsoft received the highest score of any vendor in both the strategy and current offering categories.

R2 (Green) 768 x 460px.png

Microsoft Dynamics 365 & Power Platform User Professionals

DynamicsCon is a FREE, 4 half-day virtual learning experience for 11,000+ Microsoft Business Application users and professionals.

Top Solution Authors