MDX "NOT IN <subselect>" Equivalent


e.g., have customer , time dimensions share sales facts.  purchase count , distinct customer count are measures.

how go finding count of customers purchased in 2004 , did not purchase in 2005?

the sql this:

select count (distinct customer) sales year = 2004 , customer not in (select customer sales year = 2005)

thanks!

here's adventure works example, computes count of customers made purchases in 2003 not in 2004:

select
{[measures].[customer count],
[measures].[internet sales amount]} on 0
[adventure works]
([date].[calendar year].&[2003],
except([customer].[customer].[customer].members,
nonempty([customer].[customer].[customer].members,
([measures].[internet sales amount],
[date].[calendar year].&[2004]))))
----------------------------------------
customer count internet sales amount
6,748 $6,536,073.72


- deepak


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........