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
Yggdrasill
Responsive Resident
Responsive Resident

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.     

Anonymous
Not applicable

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

v-jiascu-msft
Employee
Employee

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
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.