Hi,

I worked a little with Analysis Services and OLAP cubes, but I didn't worked
a lot with MDX expressions. When I had to do Calculated members it was just
kind of [Measures].[Total] - [Measures].[Other Total].

The problem I get now, is that I need to do a SUM or a COUNT with a
calculated member, and I really don't know how to do this. What I want to
do, is to count the number of rows in the fact table that match a certain
criteria (for example, fact_table.nb_minutes <> 0). So what I want to to is
a kind of COUNT IF on the fact table rows. I would use that to calculate an
averge for cases where nb_minutes <> 0 to have a coherent average.

Can Someone help me ? This problem occured many times, but it was always
reported to later, but I really want to solve it now.

--
Philippe Meunier
Programmer
CHCA Computer Systems Inc.

What you can do is create a new cube based on the same fact table and then
put a source table filter property on it that filters out the rows to count.
Use a COUNT measure in that cube. Then you can combine your existing cube
with this cube in a virtual cube and you should get the results you are
looking for.

Sean


--
--
Sean Boon
SQL Server BI Product Unit

**This posting is provided AS IS, with no warranties, and confers no
rights.**

"Philippe Meunier" <pmeunier (AT) chca (DOT) ca> wrote

Hello Philippe

Well, it is not so easy when you are trying to make a
query with a if-criteria on the fact table (not that I
know anyway).

An easy way of course is to create a separate dimension
that separates the cases that you want to count and not to
count, in my example dimension [ToBeCounted]. Then you
only have to create a calculated member like:

([Measures].[nb_minutes], [ToBeCounted].[Y/N].&[Y])

where nb_minutes is a count on no of rows.

This is of course not a very dynamic solution, it
basically only works if it is a fixed criteria for when to
count and not to count. I mean if you want the value that
is the criteria to be changed on the fly in the query you
need a more dynamic handling of the counting. If you want
this you could create a dimension of all possible
alternatives of nb_minutes and then just let the user
choose which ones he want to be included in the count by
choosing multiple items in the user interface.

/Martin Kihl
Scania CV AB