locked
MDX Filter a Measure with another Measure RRS feed

  • Question

  • Hi,

    I am trying to create an MDX statement that will filter one measure based on another measure's value.  The first measure is [Appointment Count] and the second measure is [Days Cancel to Appt Date].  I need an MDX statement that results in the number of appointments ([Appointment Count]) where the days between the cancel date and the appointment date are 0 ([Days Cancel to Appt Date] = 0)

    I am trying to use the filter statement, but that requires a set as its first argument...

    Thank you for your time,

    Chris

    Friday, January 7, 2011 10:59 PM

Answers

  • Hi,

    you can't filter by measure as measures are results in cube context. You can filter members of a dimension that have some measure value in the specified context.

    I suggest you create a dimension (bucket dimension) for Days Cancel to Appt Date and then you can easily filter by members of this dimension or even create a measure that will hold that value:

    with 

    member [Measures].[number of appointments where day] as ([Dim Days Cancel to Appt Date].[Days].[0], Measures.[Appointment Count])

     

    HTH,

    Hrvoje Piasevoli

    • Marked as answer by Smithochris Monday, January 10, 2011 4:48 PM
    Saturday, January 8, 2011 2:52 AM

All replies

  • Hi,

    you can't filter by measure as measures are results in cube context. You can filter members of a dimension that have some measure value in the specified context.

    I suggest you create a dimension (bucket dimension) for Days Cancel to Appt Date and then you can easily filter by members of this dimension or even create a measure that will hold that value:

    with 

    member [Measures].[number of appointments where day] as ([Dim Days Cancel to Appt Date].[Days].[0], Measures.[Appointment Count])

     

    HTH,

    Hrvoje Piasevoli

    • Marked as answer by Smithochris Monday, January 10, 2011 4:48 PM
    Saturday, January 8, 2011 2:52 AM
  • Hi Chris,

    Isn't the following MDX working for you?

    SELECT FILTER([Measures].[Appointment Count], [Measures].[Days Cancel to Appt Date] = 0) ON Columns

    FROM

     

    <YourCube>

    Thanks,
    Santosh

     

     

     

     

    Saturday, January 8, 2011 3:09 AM
  • Hi,

    @Santosh Have you actually tried what you suggest?

    Using this syntax you either remove members from the first set - that would be Measures.[Appointment Count] - or you leave it and get the total value. And the filter part is evaluated for all member (or default member) for each hierarchy in the measure group. Assuming the aggregation formula is sum, if only one fact record is <> 0 the filter would discard the first member - in this case the measure.

    You really need to go to the measure group granularity  ( or even lower if a single fact record can not be selected using dimension key attributes) to filter fact rows that have 0 in this measure and return the count of rows of that filtered set.

    Regards,

    Hrvoje Piasevoli

    Saturday, January 8, 2011 4:03 AM
  • Thank you for the responses.  Santosh's suggestion was not working correctly for me.  I followed Hrvoje's suggestion to create "buckets" for the days measure, and this works as I want it to.  Thank you for your help!

     

    Chris

    Monday, January 10, 2011 4:46 PM