none
DAX / MDX formulas for PowerPivot

    General discussion

  • Hi there,

    I'm trying to develop a MDX calculated measure. I want to look at the most recent pathology results for a patient; however, a patient can have multiples of the same test done on different dates within the same hospital admission. I want Excel to give me the test result for the Pathology Date that is the closest to the Discharge Date.

    To throw a spanner in the works, the test results field can be either numerical or alphabetic, in this case, I only want to look at the numerical results.

    These are the field names in the OLAP PowerPivot:

    [dim_episode].[Episode Admission System Date]

    [dim_pathology].[Pathology Doc System Date]

    [dim_episode].[Episode Discharge System Date]

    [dim_episode].[Prvsp Refno] (this is the patient identifier)

    [dim_pathology].[Result]


    And I have tried the following formula with no luck:
    =MAXX(Filter([dim_episode], [dim_episode].[Prvsp Refno] = EARLIER([dim_episode].[Prvsp Refno])), [dim_pathology].[Result])

    Thanks in advance for your help!

    A.

    Monday, November 18, 2013 2:09 AM

All replies