How to get the Max value from an intermidiate table by using DAX

See more:

SQL-Server-2012

MDX

Hi,

I have below tables in a tabular data base, and I want to get the max favorite price range for each member.

DimMember(MemberKey int,MemberName varchar(50)
DimPriceRange(PriceRangeKey,PriceRangeValue varchanr(50))
FactOrders(FactOrderKey int,MemberKey int,PriceRangeKey)

The DAX query I tried to use is below, but it emits an error when execution, can someone help me out, thanks!

the error states cannot determine a table that contains [OrderCount]

evaluate
	summarize (
		summarize (
			factorders,
			factorders[memberkey],
			factorders[pricerangekey],
			"OrderCount",countrows(factorders)
		),
		factorders[memberkey],
		"MaxOrderCount",Max([OrderCount])
	)

The interesting thing is that below DAX query can be executed if don't put [OrderCount] in the Max function.

evaluate
	summarize (
		summarize (
			factorders,
			factorders[memberkey],
			factorders[pricerangekey],
			"OrderCount",countrows(factorders)
		),
		factorders[memberkey],
		[OrderCount]
	)

below is the execution result:

FactOrders[MemberKey]   [OrderCount]
1   1
3   2
2   2
3   1
1   2
2   1

what I want is below, which only returns the max [OrderCount] for each member.

FactOrders[MemberKey]   [OrderCount]
3   2
2   2
1   2