CODEWITHSUNDEEP
countconditional-statementsssasmdx
KenZ
KenZ

Reputation: 45

Conditional records count with MDX, SQL Server BI

This may be a simple MDX question but I've spent 2 days without any luck.

I have a simple fact table with the following columns:

ID        state        type        price

001        CA          TRUCK       50300
002        MA          BIKE        3010
003        MA          BOAT        0
004        CO          BOAT        20100
...        ...

I have a cube with 2 dimensions, state and type. Now I'd like to get a count of rows for these two dimensions where price > 0. Can I do it without creating a 3rd dimension (id)? How would I go about doing this? Thanks!

Upvotes: 0

Views: 1313

Answers (1)

Boyan Penev
Boyan Penev

Reputation: 854

You can first create a new column in your fact table/view -> CountRows which is 1 where the Price is > 0 and NULL otherwise. E.g. (in T-SQL):

...
CASE
    WHEN Price > 0 THEN 1
    ELSE NULL
END CountRows
...

Then, create a new measure with Sum aggregation type on top of this column and you should be good to go. In the example above, this new measure will give you 1 for State.MA and for Type.Boat.

Upvotes: 1

Related Questions