Hello!
I have a report made over a powercube, with a table like this:
ITEM1: 1
ITEM2: 1
ITEM3: 0
ITEM4:
ITEM 5: 1
ITEM 6: 1
ITEM 7: 1
ITEM 8:
ITEM 9:
ITEM 10: 0
Each item is a diferent measure in the cube, and the table is previously filtered by dimensions using value prompts. The measures always have values 1, 0 or no value (NULL from the database). I need to create 2 calculated measures, one of them must sum all the values and show the result, and the other must count how many of them have a value (0 or 1). So, in the example, the first calculated measure must show 5 and the second calculated measure must show 7.
I tried to make the fist one like this:
[ITEM1] + [ITEM2] + [ITEM3] + [ITEM4] + [ITEM5] + [ITEM6] + [ITEM7] + [ITEM8] + [ITEM9] + [ITEM10]
But it only works when all items have a value (0 or 1), but when there's an item without value, it shows nothing.
And I have no idea how to make the second one.
Any idea?
each data item has different measures?
or each item is a different data item?
is this a crosstab?
what are 1,0, and no value? isnt this the output of a certain measure?
Each data item is a different measure. This is not a crosstab, they are shown in singletons.
But I've solved it!
For the first I've made a calculated measure like this:
IF ( [ITEM1] is not null ) THEN
( [ITEM1] )
ELSE
( 0 )
And for the second one I did:
IF ( [ITEM1] is not null ) THEN
( 1 )
ELSE
( 0 )
Thanks!
coalesce ([ITEM1],0) is better way.