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)
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])
Microsoft is conducting an online survey to understand your opinion of the Technet Web site. If you choose to participate, the online survey will be presented to you when you leave the Technet Web site.