mdxdimensionfact

How to filter a measure by two dimension members


I have a very simple question that I can't resolve alone. Here it is: say you have a PERSON fact, with a field Birthdate which is linked to a dimension DATE correctly filled, and with the measure "People Number". In MDX, how can I get the people number born at 01/01/2018 and at 01/01/2019, on one line? We should have the following result:

--------|People Number|

Born--|---------25--------| <-- one line with the correct result

It is forbidden to alter the PERSON fact, and to add a second "date" field for example. The annoying thing, for me, is that I can't create a tuple with many dates of the Date dimension. I can't see how to use the filter function, as I want the result on one line.

thank you.


Solution

  • How about using a subquery with the two dates?

    Here is an example using an imaginary sales cubes that selects the Amount value for a given Region (i.e., Europe) and two days only:

    select 
       [Measures].[Amount] on 0,
       [Customers].[Geography].[Region].[Europe] on 1
       
       from ( 
          select {
                [Time].[Calendar].[Day].[8 Feb 2005],
                [Time].[Calendar].[Day].[10 Feb 2005]
            } on 0 
          
          from [Sales]
        )