CODEWITHSUNDEEP
ssasmdx
Gemione
Gemione

Reputation: 65

MDX Query WHERE Dimension = another Dimension

If I have two Dimensions: Origin and Destination how can I count the number of times Origin = Destination?

SELECT [Location] ON COLUMNS
FROM [DELIVERIES]
WHERE ( [Origin] = [Destination] )

[Origin] = [Destination] obviously isnt the answer...

The following code produces the correct result:

SELECT
    SUM(Transfers) AS Same_Skill_Transfers
FROM (

    SELECT 
        CONVERT(varchar(30),"[From VQ].[LOB].[LOB].[MEMBER_CAPTION]") As From_VQ, 
        CONVERT(varchar(30),"[To VQ].[LOB].[LOB].[MEMBER_CAPTION]") As To_VQ, 
        CONVERT(integer,"[Measures].[Transfers]") As Transfers
    FROM
        OPENQUERY(TRANSFERS,'
            SELECT 
                {[Measures].[Transfers]} ON COLUMNS,
                Filter(NonEmptyCrossjoin(  [From VQ].[LOB].Members,  [To VQ].[LOB].Members),
                            [From VQ].[LOB].[LOB].[Life Cycle].Properties(''Caption'') =  [To VQ].[LOB].[LOB].[Life Cycle].Properties(''Caption'')
                ) ON 1
            FROM 
                [Transfers]
            WHERE (
                [Date].[Date Hierarchy].[Month].[July 2014], 
                [From VQ].[Hierarchy].[AOB].[Consumer],
                [From Agent].[Employee Id].&[612117]
            )

        ')
) A
WHERE
    To_VQ = From_VQ

Lets say I have a [FROM_VQ].[LOB] = "BOTH" and I want to include in the SUM when [FROM_VQ].[LOB] = "Both" and [TO_VQ].[LOB] is either A or B?

Upvotes: 1

Views: 758

Answers (1)

mmarie
mmarie

Reputation: 5638

Your question is missing some info, but I'll take a stab and then update once you provide more info. I'm assuming that there are two dimensions, origin and destination, and that each of these dimensions have a location attribute.

In general, you can use a filter statement for this. If your location attribute has the same key in each dimension, you can do this: 

select [Origin].[Location].children on 0, 
Filter(NonEmptyCrossjoin( [Origin].[Location].Children, [Destination].[Location].Children), 
[Origin].[Location].Properties('Key') = [Destination].[Location].Properties('Key')
) on 1 from [DELIVERIES]

If the keys aren't the same, but the label you see for the field is you can switch Key for Caption.

Here's an example where someone did this with dates.

Upvotes: 3

Related Questions