Search code examples
conditional-statementsssascube

SSAS Conditional cube calculated member

I'm new to SSAS and cubes.

I have a simple database table: 

product|value|status  
-------|-----|------  
A|100|1  
B|20|0  
A|20|0  
B|80|1

Status field denotes whether it is planned sale or already closed one.

I'd like to have measures in the cube for Planned (0) sale and Closed(1) sale. They should sum value per product in given status.

Output cube should look as follows: 

product|planned sale|closed sale    
------|---------|-----------  
A|20|100  
B|20|80

I have measure for value and status. However, no matter how I slice the function, boolean in status is aggregated. I guess my measure is configured wrong. I tried setting it to boolean, however it appears to do "and" computation.

I tried IFF and .currentmember hoping it would iterate.

I must be missing something basic, please help. Thank you.

Solution

  • Add a simple Status dimension, with 0 for Planned and 1 for Closed.

    It's getting aggregated because right now you're using it as a measure.

    EDIT based on your comment:

    I believe you are incorrectly assuming that SSAS will interpret the value of measures.status as a Boolean. SQL does not have a concept of a Boolean datatype. Although the bit datatype can be used to hold Boolean values, in SQL you cannot say IF [MyBitColumn] THEN ... Instead you have to say IF [MyBitColumn]=1 THEN .... SQL Server does NOT automatically understand/assume that 1 = true, and 0 = false.

    Try this for your calculated measure:

     iif([Measures].[Status]=1, [Measures].[Value],0)  //closed sale
 iif([Measures].[Status]=0, [Measures].[Value],0)  //planned sale