0

I currently have the following code in MDX that I've been struggling on getting formatted in the way I need.

WITH

MEMBER measures.Quantity_Chicago_Madison AS
    IIF([Tb Consumer].[City].currentmember is [Tb Consumer].[City].& 
    [Chicago] and [Tb Supplier].[City].currentmember is [Tb Supplier].[ 
    City].&[Madison], [Measures].[Quantity - Tb Transactions], null)

MEMBER measures.Quantity_Madison_Chicago AS
  IIF([Tb Consumer].[City].currentmember is [Tb Consumer].[City].&[Madison] 
  and [Tb Supplier].[City].currentmember is [Tb Supplier].[City].&[Chicago]  
   , [Measures].[Quantity - Tb Transactions], null)

SELECT non empty {
measures.Quantity_Madison_Chicago,measures.Quantity_Chicago_Madison
} ON COLUMNS, non empty

{  [Tb Product].[Name].[Name].ALLMEMBERs*[Tb Consumer].[City].allmembers* 
[Tb 
Supplier].[City].allmembers} ON ROWS

FROM [DS715]

I am getting the following output from running this query. Current Output

Ideally, I want my output to be the following: Ideal Output

I feel like I've tried everything, but I know there's one piece of the puzzle that I can't quite figure out. Any help would be so great. Thank you

peakstatus
  • 381
  • 1
  • 3
  • 15

1 Answers1

0

Please try below script:

WITH

MEMBER measures.Quantity_Chicago_Madison AS
    ([Tb Consumer].[City].&[Chicago] * [Tb Supplier].[City].&[Madison] , [Measures].[Quantity - Tb Transactions])

MEMBER measures.Quantity_Madison_Chicago AS
  ([Tb Consumer].[City].&[Madison] * [Tb Supplier].[City].&[Chicago] , [Measures].[Quantity - Tb Transactions])

SELECT non empty {
measures.Quantity_Madison_Chicago,measures.Quantity_Chicago_Madison
} ON COLUMNS, non empty

{  [Tb Product].[Name].[Name].ALLMEMBERs } ON ROWS

FROM [DS715]
Delphie
  • 56
  • 4