Convert sql query to MDX

  • 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)

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

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

Viewing 2 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply