Sql-server – having ‘Measure’ > 0 syntax does not work in mdx query

You need first to define a measure that will compte the median price across the siblings:

[Measures].[Siblings_median_price] 
AS 
MEDIAN([DimItems].[Dim Items Id].CurrentMember.Siblings, [Measures].[Price])

The following measure should return what you want:

[Measures].[Sum_below_median] 
AS 
SUM(
     [DimItems].[Dim Items Id].CurrentMember.Children
   , IIF([Measures].[Price] < [Measures].[Siblings_median_price]
   , [Measures].[Quantity], Null)
)

I tried to duplicate what you have using a multidimensional Contoso cube with a measure that is Min Inventory Day in Stock and didn't encounter your issue. I think it has to do with the current context when you aggregate [adhoc]. I don't have enough info to duplicate exactly. But this phrase caught my eye when looking at the specifications for MIN():

If a numeric expression is not specified, the specified set is evaluated in the current context of the members of the set and then returns the minimum value from that evaluation.

First, a few thoughts:

• As a note, you don't need single quotes around your calculated members in your query. As long as your calculation doesn't throw an error, it doesn't hurt for them to be there, but they aren't needed after SSAS 2005 or so and can suppress messages in some cases.
• Calculated members are evaluated at run time so you can't take advantage of any caching. It's better to move the work into a scoped script and keep the calculated measure simple. Or move them to into the axes if possible so they can be cached with the query as it is run multiple times. If you have a large dataset or very critical data that must be returned quickly you might want to change your use of calculated members.
• Your UNION with DESCENDANTS is fine. I was able to do something similar and it produced the correct results. You can check this by using the set instead of the aggregated member and confirming that all the members are present as you expected. I'm a little puzzled why you have it, though. Correct me if I'm wrong, but I thought Wesser Ems is part of Lower Saxony (I'm not completely knowledgeable on German geography, though). If Wesser Ems is part of Lower Saxony, you could have just done the descendents of Lower Saxony and not done the union. That is neither here nor there since there is nothing wrong with your union. I just like to try to simplify queries as much as possible.
• SSAS is really forgiving. It will try to interpret what you mean when you don't specify a fully qualified member. For instance, you use [Has Data] rather than [Measures].[HasData], and your set and aggregated member are both [adhoc]. If there is also a field called [HasData] somewhere, it can get confused as to what you meant.
• SSAS ignores nulls when calculating the min
• You already have a measure that performs the MIN() ([Measures].[Has Data]), so using AGGREGATE() will continue to do the min, if that is what is in the current context.
• Unlike the WHERE clause in SQL, the WHERE clause of an MDX SELECT statement never directly filters what is returned on the Rows axis of a query. To filter what appears on the Rows or Columns axis of a query, you can use a variety of MDX functions, for example FILTER, NONEMPTY and TOPCOUNT. For instance, if I don't have any inventory in 2006, 2006 is still returned as a member in the following query:

WITH SET [adhoc] AS 
UNION(DESCENDANTS([Geography].[Geography Hierarchy].[Region Country Name].[Germany], 
[Geography].[Geography Hierarchy].[City Name]), 
DESCENDANTS([Geography].[Geography Hierarchy].[State Province Name].[Lower Saxony], 
[Geography].[Geography Hierarchy].[City]))
MEMBER [Geography].[Geography Hierarchy].[adhoc] as MIN([adhoc])   
SELECT {[Date].[Calendar YQMD].[Calendar Year].members } on 0 
from [Operation]
where ([Geography].[Geography Hierarchy].[adhoc],[Measures].[Inventory Min Day In Stock])

In my dataset, my fact table only has a value of 5 for the data that makes up [Measures].[Inventory Min Day In Stock]. This is similar to you having all 1s.

This query returns the same expected answer (5) for me whether I use MIN([adhoc]) or AGGREGATE([ad hoc])

WITH 
SET [adhoc1] AS 
UNION(DESCENDANTS([Geography].[Geography Hierarchy].[Region Country Name].[Germany], [Geography].[Geography Hierarchy].[City Name]), 
DESCENDANTS([Geography].[Geography Hierarchy].[State Province Name].[Lower Saxony], [Geography].[Geography Hierarchy].[City]))
MEMBER [Geography].[Geography Hierarchy].[adhoc] as Min([adhoc1])
SELECT {[Date].[Calendar YQMD].[Calendar Year].[Year 2008] } on 0
from [Operation]
where ([Geography].[Geography Hierarchy].[adhoc],[Measures].[Inventory Min Day In Stock])

My best advice is to try rewriting it a couple of different ways. If the calculated measures aren't too slow, you can use them to make sure you have the correct context.

WITH 
MEMBER [Geography].[Geography Hierarchy].[adhoc] as Aggregate(UNION(DESCENDANTS([Areas].[Hierarchy].[Region].[Weser Ems],[Areas].[Hierarchy].[City]),DESCENDANTS([Areas].[Hierarchy].[State].[Lower Saxony],[Areas].[Hierarchy].[City])))
MEMBER [Measures].[Test] as Min([Geography].[Geography Hierarchy].[adhoc],[Measures].[HasData])
SELECT {[Time].[Hierarchy].[Year].[2008] } on 0
from [Incidence]
where ([Measures].[Test])