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
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
Post a Comment