Warm tip: This article is reproduced from serverfault.com, please click

Get Distinct User Count Over Particular Time MDX QUERY

发布于 2021-02-20 05:14:12

I need some mdx help.

Cube Details:

Measures.Users -> Distinct Count on Users.

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 ?

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)
Questioner
DevHelp
Viewed
0
Mike Honey 2021-02-25 13:49:13

The built-in distinct count measure gives the most flexibility. It sounds like you already have one as Measure.Users? Is the measure group for Users connected to your dimensions for Date and ShopLocations?

To help get that working, I would review the Distinct Count pattern in the Many-to-Many Revolution paper. That approach gives a no-code solution that is more flexible and probably faster to run:

https://sqlbi.com/whitepapers/many2many