I have MDX query which throws an error - "The portalID hierarchy is used more than once in the cross Join Function"
The query fails at the where clause
WHERE
(-{[SG Location].[PortalID].[30],
[SG Location].[PortalID].[46]},
[SG Location].[PortalID].[4],
[SG Program].[Campaign].&[*Cymbalta_Lilly (111)],
[Prints],
[SG Location].[IsTest].[0]
)
In the where clause above following condition (Exclude few portals) is appended dynamically
-{[SG Location].[PortalID].[30],
[SG Location].[PortalID].[46]}
Please help! I am new to MDX queries. Any useful link related to MDX queries - about where clause specifically - is really appreciated.
Below is the complete MDX query
WITH
MEMBER [SG Date].[Day].[TotPrintCount] AS
SUM(
{
[SG Date].[Day].&[2018-06-15],
[SG Date].[Day].&[2018-06-16],
[SG Date].[Day].&[2018-06-17],
[SG Date].[Day].&[2018-06-18]
},
[Measures].[Prints]
)
MEMBER [SG Date].[Day].[TotCustRev] AS
SUM(
{
[SG Date].[Day].&[2018-06-15],
[SG Date].[Day].&[2018-06-16],
[SG Date].[Day].&[2018-06-17],
[SG Date].[Day].&[2018-06-18]
},
[Measures].[CustRev]
)
MEMBER [SG Date].[Day].[TotLDMRev] AS
SUM(
{
[SG Date].[Day].&[2018-06-15],
[SG Date].[Day].&[2018-06-16],
[SG Date].[Day].&[2018-06-17],
[SG Date].[Day].&[2018-06-18]
},
[Measures].[LDMRev]
)
MEMBER [Measures].[RevRatio] AS [Measures].[LDMRev] / [Measures].[Transactions] * 1000
MEMBER [Measures].[MatchRatio] AS SUM([Measures].[PaidPrints]) / SUM([Measures].[Transactions]) * 100
SELECT {
[SG Date].[Day].[TotPrintCount],
[SG Date].[Day].&[2018-06-15],
[SG Date].[Day].&[2018-06-16],
[SG Date].[Day].&[2018-06-17],
[SG Date].[Day].&[2018-06-18],
[SG Date].[Day].[TotCustRev],
[SG Date].[Day].[TotLDMRev]}
ON COLUMNS, NON EMPTY
{
{ [SG Location].[Hierarchy].[Portal].ALLMEMBERS } * { [SG Program].[Hierarchy].ALLMEMBERS }
} ON ROWS FROM [ScriptGuide]
WHERE
(-{[SG Location].[PortalID].[30],
[SG Location].[PortalID].[46]},
[SG Location].[PortalID].[4],
[SG Program].[Campaign].&[*Cymbalta_Lilly (111)],
[Prints],
[SG Location].[IsTest].[0]
)