Counting Rows

I want to define a measure which is count of rows in a fact table satisfying certain criteria. I just need a single value and hence this measure need not be additive from any dimension.

There are 2 possible scenarios -

(1) The criteria involves columns from the fact table itself (Example: [Fact Table].[Transaction Type] = 1 or 2 or 3)

(2) The criteria involves columns from dimension tables (Example: [Dimension Table].[Transaction Category] = 'D')

I am novice to SS BI and I have tried-out various solutions involving MDX Queries, Calculated Member, Named Set etc. but not successful. Kindly suggest me, how to achieve this.

Thanks & Regards.

Is my question so naive?|||

Maybe the best solution (for performance and smart use) should be to create an additive measure.

Try it in this way:

1) Create a Named Calculation in your fact table in your data source view

2) In the Expression write a CASE to test your condition to have 1=True and Null=False, something like this:

CASE
WHEN ([Transaction Type] = 1) THEN 1
WHEN ([Transaction Type] = 2) THEN 1
WHEN ([Transaction Type] = 3) THEN 1

WHEN ([Transaction Category] = 'D') THEN 1
ELSE NULL
END

3) In your cube create an additive measure based on your Named Calculation

Now you should use directly your measure in every MDX query.

Let me know if it work fine.

Francesco

|||Another approach is to create a row count measure and create a dimension based on Transation Type in addition to the with the dimension containing Transaction Category. You can then filter the count on Transaction Type and Transaction Category at run-time. Or, if you never wanted to see a certain type of transaction, you could put in a named query in your DSV or a query binding in your partition that filters out those rows.