SQL Server Performance Forum – Threads Archive

Exclude null values from aggregates

It seems that OLAP treats nulls as 0 for the purpose of min/max aggregate. KB Q 244650 If I define a Measure which uses min, the null values become 0 and it affects the result.
Because I have to exclude null from the min. I cannot use a calculated member with IIF and excluding null there because I have to go down too deep in the dimension (to get the min berffore the aggregations) and it becomes extremely low. I don’t undestand how this basic and pretty simple thing cannot be done in a simple way.
sure I can prefilter in the fact table the nulls an put a dummy extremely large value so the nulls (now converted to a large value) don’t affect the min.
but that seems too cumbersome. Is there an easy way ?

---

There are many approaches you can use. One way is to use the NOT ISEMPTY function in your expression to filter out the empty cells, also set the NON EMPTY BEHAVIOR property accordingly in advanced properties for your calculated measure. Raulie

---

]]>