can't seem to get a nested iif to work


hi all,

i'm trying measure display except dimension payment method = direct debit or renewal payment method = direct debit i.e.

payment method    renewal payment method    result
direct debit            cash                                       null
direct debit            direct debit                            null
cash                        direct debit                           null
cash                        cash                                    1 (i'll replace 1 calculation on measure)
cash                        cheque                                1

its counting how many still have converted payment method direct debit.

i thought might this;
iif([payment method].[payment method].currentmember [payment method].[payment method].[direct debit],0,
(iif([renewal payment method].[renewal payment method].currentmember [renewal payment method].[renewal payment method].[direct debit],0, 1))

which hoping follow logic; if payment method = direct debit, measure = 0, else if renewal payment method = direct debit (and payment method <> direct debit previous if excluded it), measure =  0, else measure =  1.

any appreciated.

regards,
phil

after. 

iif([payment method].[payment method].currentmember [payment method].[payment method].[direct debit] or
 [renewal payment method].[renewal payment method].currentmember [renewal payment method].[renewal payment method].[direct debit]
 ,0, 1)
 
tried similar thing on adventure works

with set [selectedproducts] as   
{[product].[product categories].[product].&[344],   
[product].[product categories].[product].&[348],  
[product].[product categories].[product].&[310],  
[product].[product categories].[product].&[220]}   
 
member [measures].[a] as   
iif([product].[product categories].currentmember is [product].[product categories].[mountain-100 silver, 38]   
 ,0, 1)  
   
member [measures].[b] as   
iif([date].[calendar year].currentmember is [date].[calendar year].[all periods]   
 ,0, 1)  
   
member [measures].[c] as   
iif([product].[product categories].currentmember is [product].[product categories].[mountain-100 silver, 38]  
or  [date].[calendar year].currentmember is [date].[calendar year].[all periods]  
 ,0, 1)  
 
select{[a], [b], [c]} on 0,  
nonempty([selectedproducts]* [date].[calendar year].members) on 1  
from [adventure works]  
 


SQL Server  >  SQL Server Analysis Services



Comments

Popular posts from this blog

Conditional formatting a graph vertical axis in SSRS 2012 charts

Register with Power BI failed

SQL server replication error Cannot find the dbo or user defined function........