SQL Server Performance

Exclude null values from aggregates

Discussion in 'Analysis Services/Data Warehousing' started by gus, Jun 30, 2006.

  1. gus New Member

    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 ?
  2. Raulie New Member

    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



Share This Page