MDX: iif condition on the value of dimension

2.6k views Asked by At

I have 1 Virtual cube consists of 2 cubes. Example of fact table of 1st cube.

id      object_id     time_id     date_id        state
1        10              2          1               0
2        11              5          1               0
3        10              7          1               1
4        10              3          1               0
5        11              4          1               0
6        11              7          1               1
7        10              8          1               0
8        11              5          1               0
9        10              7          1               1
10       10              9          1               2

Where State: 0 - Ok, 1 - Down, 2 - Unknown

For this cube I have one measure StateCount it should count States for each object_id. Here for example we have such result:

for 10 : 3 times Ok , 2 times Down, 1 time Unknown for 11 : 3 times Ok , 1 time Down

Second cube looks like this:

id      object_id     time_id     date_id        status
1        10              2          1               0
2        11              5          1               0
3        10              7          1               1
4        10              3          1               1
5        11              4          1               1

Where Status: 0 - out, 1 - in. I keep this in StatusDim.

In this table I keep records that should not be count. If object have status 1 that means that I have exclude it from count.

If we intersect these tables and use StateCount we will receive this result:

for 10 : 2 times Ok , 1 times Down, 1 time Unknown

for 11 : 2 times Ok , 1 time Down

As far as i know, i must use calculated member with IIF condition. Currently I'm trying something like this.

WITH MEMBER [Measures].[StateTimeCountDown] AS(
  iif(
    [StatusDimDown.DowntimeHierarchy].[DowntimeStatus].CurrentMember.MemberValue 
         <> "in"
  , [Measures].[StateTimeCount]
  , null )

)

1

There are 1 answers

0
FrankPl On

The multidimensional way to do this would be to make attributes from your state and status columns (hopefully with user understandable members, i. e. using "Ok" and not "0"). Then, you can just use a normal count measure on the fact tables, and slice by these attributes. No need for complex calculation definitions.