Hi

I have a fact table that captures the captures oldest date between a Task date and todays date per Person (so there is only fact per person) like this - a measure is created from the Task Days

PersonID, Dateduekey, Task Days

130          20130809     679

I have a person dimension which has a hierarchy of Department_Name->Team_Name->Person

I have created some MDX that gives the MAX Task Day for the hierarchy but when you Person attribute of the hierarchy the code runs really slow.

with set a as nonempty(([v Dim Fee Earners].[People Structure].[Person],[Dim Calendar].[Primary Date].[All]),[Measures].[Days KPI])
member measures.a as max(existing(a),[Measures].[Days KPI])

How can I get this running for more efficiently when [v Dim Fee Earners].[People Structure].[Person] is selected? To be fair the true measure [Days KPI] is already calculated at person level so if there is way to use the [Days KPI] when [v Dim Fee Earners].[People Structure].[Person] is selected that would be good

Can you not use the head/tail function?

I'm not sure if below works, but maybe someone can tweak it if it's a valid solution?

WITH SET a AS 
HEAD(
	Exists(
		ORDER(
			NonEmpty(
				([v Dim Fee Earners].[People Structure].[Person]
					,[Dim Calendar].[Primary Date].[All])
			,[Measures].[Days KPI]
			)
		,[Measures].[Days KPI] DESC) //maybe BDESC
	,{[Measures].[Days KPI]})
,1).item(0)

Maybe u have to add .membervalue at the end

Can you not use the head/tail function?

I'm not sure if below works, but maybe someone can tweak it if it's a valid solution?

WITH SET a AS 
HEAD(
	Exists(
		ORDER(
			NonEmpty(
				([v Dim Fee Earners].[People Structure].[Person]
					,[Dim Calendar].[Primary Date].[All])
			,[Measures].[Days KPI]
			)
		,[Measures].[Days KPI] DESC) //maybe BDESC
	,{[Measures].[Days KPI]})
,1).item(0)

Maybe u have to add .membervalue at the end

Thanks , but that doesn't quite work as I need to the max value for each [v Dim Fee Earners].[People Structure].[Person]

As I only get the slow performance on the lowest level I came up with the following

 member measures.b as IIF([v Dim Fee Earners].[People Structure].currentmember.level.ordinal = 3,Measures.[Day KPI],Measures.a)

This worked fine but because the user front end is performancepoint, if you then apply a set in the where clause for [v Dim Fee Earners].[People Structure].[Person] you get a error in the value be cause currentmember can't handle multi values.

Not sure how to get around this?

U could change the measure to use 

WITH SET a AS 
HEAD(
	Exists(
		ORDER(
			NonEmpty(
				([v Dim Fee Earners].[People Structure].Currentmember
					,[Dim Calendar].[Primary Date].[All])
			,[Measures].[Days KPI]
			)
		,[Measures].[Days KPI] DESC) //maybe BDESC
	,{[Measures].[Days KPI]})
,1).item(0)
Then in the select on rows u use the [v Dim Fee Earners].[People Structure].[Person].

Hi,

Please try this way,

with member [Measures].[MaxValue] as

max(filter([Product].[Product].[Product],[Measures].[Internet Sales Amount]),[Measures].[Internet Sales Amount])
//max(filter([Product].[Product].[Product],[Measures].[Internet Sales Amount]<0),[Measures].[Internet Sales Amount])

select [Measures].[MaxValue] on 0

from
[Adventure Works]

Regards,

Manish