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.
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.