mdx - How to filter a measure by two dimension members

Question

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.

question from:https://stackoverflow.com/questions/65645301/how-to-filter-a-measure-by-two-dimension-members

Answer 1

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]
    )