Convert SQL query to MDX - have Group by & Count Functions

459 views Asked by At

I have the following SQL query which I am trying to convert into MDX:

select avg(skucount)
from
(
SELECT count(distinct [SKUCode]) as skucount

      --,[SHOPCODE_WITHOUT_DIST]

  FROM [HFPL_DW].[dbo].[FactSecondarySales]
  where DISTCODE in
  (
  SELECT [DISTRIBUTORCODE]
  FROM [HFPL_DW].[dbo].[DimDistHierarchy]
  where REGION = 'KARACHI'
  )
  and month(saledate) = 7 and year(saledate) = 2018 
  group by [SHOPCODE_WITHOUT_DIST]

  ) as inner_query

The inner query returns the count of SKU saled on each shop(which is fulfilled by using "Group by ShopCode")

First I am trying to convert the inner query to MDX, I have tried the following:

WITH MEMBER [Measures].[SKU Count] AS
    COUNT( NonEmpty( {  [Product Hierarchy].[SKU].[SKU].Members }, ( [Shop Hierarchy].[SHOPCODE WITHOUT DIST] ) )   )
SELECT
    {
        [Measures].[SKU Count]
    } ON COLUMNS,
    NonEmpty(
        { [Product Hierarchy].[SKU].[SKU].Members },
        ([Shop Hierarchy].[SHOPCODE WITHOUT DIST] )
    ) ON ROWS
FROM
    [Consolidated Sales]
    where(
    [Time Analysis].[Month].&[2018-07-01T00:00:00],

[Distribution Hierarchy].[DISTRIBUTORCODE].&[1002]
)

Reference: https://social.msdn.microsoft.com/Forums/sqlserver/en-US/51988607-78cc-4520-88db-c6d3e99dd1fc/mdx-to-count-the-number-of-members-in-a-dimension-based-on-another-dimension?forum=sqlanalysisservices

It is not returning anything.

Kindly help me acheive the desired output of the Average SKUs saled(The outer query), the number of SKUs saled per shop (inner query)

0

There are 0 answers