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)