Convert sql query to MDX

• Ninjahat

  SSC Enthusiast

  Points: 172

  More actions

  February 22, 2021 at 10:25 pm

  #3848724

  I want to find an mdx equivalent of this query:

  Select a.shopId , Month(TransactionDate) Month_Transaction,

  Year(TransactionDate) Year_Transaction,

  count(distinct b.UserID) UniqueUserCount

  FROM [dbo].[shop] a

  JOIN users b ON a.UserID = b.UserID

  where TransactionDate >= '2018-01-01'

  Group by a.shopId ,Month(TransactionDate), Year(TransactionDate)

   

  This is what I have so far which produces unique count irrespective of date. I want unique count in the date range. Pls let me know how to achieve this ?

  // Note: Date range can be anything.

  // Measures.Users ->  distinct count of userId

  SELECT {

  [Date].[Month].&[2020]&[2020-Q3]&[2020-09],

  [Date].[Month].&[2020]&[2020-Q4]&[2020-10],

  [Date].[Month].&[2020]&[2020-Q4]&[2020-11],

  [Date].[Month].&[2020]&[2020-Q4]&[2020-12]

  } ON COLUMNS, NON EMPTY

  {

  [ShopLocations].[Hierarchy].[Shop]

  } ON ROWS

  FROM [ShopperCube]

  where (Measures.Users)

• Site Owners

  SSC Guru

  Points: 80330

  More actions

  February 23, 2021 at 11:10 pm

  #3849250

  Thanks for posting your issue and hopefully someone will answer soon.

  This is an automated bump to increase visibility of your question.