SQL Server Performance

MDX Distinct count of Model&Color in Year Dimenson

Discussion in 'SQL Server 2005 Analysis Services' started by cemuney, Jan 29, 2007.

  1. cemuney New Member

    Hi everyone.
    suppose that i have 3 dimensions as.

    ProductModel and Color
    and SalesFact table.

    i want to find the distinct count of ProductModel+Color.
    i mean.
    Year/ProductModel/Color/Sales
    ---------------
    2006/010101 /White /1.0
    2006/010101 /Black /2.0
    2007/010101 /White /1.0

    2006/010102 /White /1.0

    2007/010102 /Black /3.0
    2007/010103 /White /2.0

    i am trying to add CalculatedMember in AS2000.
    i can easily count ProductModel with.
    member formula...

    filter(descendants([ProductModel ],[ProductModel ].[Model Id]),[Measures].[Sales]<>0).count)


    and i can get. By year and Model
    2006 - 010101 - 2
    2007 - 010101 - 1
    2006 - 010102 - 1
    2007 - 010102 - 1
    2007 - 010103 - 1

    But i want to get distinct count of ProductModel+Color by Year Dimension
    i mean.
    i want see.
    Year/ ModelCount/Model+colorCount

    2006 - 2 - 3
    2007 - 3 - 3

    it means
    2006 - 2(010101&010102) - 3 (010101White&010101Black&010102White)
    2007 - 3(010101&010102&010103) - 3 (010101White&010102Black&010103White)
    i hope someone understand my question..
  2. satya Moderator

    Have you tried the BIDS to build such mDX query, it is achievable using that tool with a AS project.

    Satya SKJ
    Microsoft SQL Server MVP
    Writer, Contributing Editor & Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided AS IS with no rights for the sake of knowledge sharing. The greatest discovery of my generation is that a human being can alter his life by altering his attitudes of mind.
  3. cemuney New Member

    <br />Yes i need an &lt;&lt;expression&gt;&gt; to write in BIDS.<br />&lt;&lt;Calculations&gt;&gt; tab, &lt;&lt;New calculated member&gt;&gt; , &lt;&lt;expression&gt;&gt;..<br /><br />am i missing somethig?<br />is there another tool in BI Development Studio to build MDX queries?<br /><br />i just want to see distinctcount of 2 dimensions.<br />i should not be so difficult.<br /><br />DISTINCTCOUNT( «Set» ) ?????<br />i need an set expression<img src='/community/emoticons/emotion-6.gif' alt=':(' /><br /><br />

Share This Page