CODEWITHSUNDEEP
ssasmdxolapcube
DevHelp
DevHelp

Reputation: 305

Get Distinct User Count Over Particular Time MDX QUERY

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)

Upvotes: 1

Views: 773

Answers (2)

Mike Honey
Mike Honey

Reputation: 15027

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

Upvotes: 2

Subbu
Subbu

Reputation: 2205

You can use the DistinctCount MDX function.

The official documentation is not very clear, but, the general principal is: You pass in a "Set" to this function to get the distinct values.

A sample MDX

WITH SET MySet AS
    { [Dim User].[User Id].Children } 
 
MEMBER Measures.SetDistinctCount AS
    DISTINCTCOUNT(MySet)

SELECT { Measures.SetDistinctCount, Measures.Amount }  ON 0
       , { [Dim Date].[Date Key].AllMembers } ON 1
FROM [Mine]

To validate this, the following is my setup:

enter image description here

Query result

enter image description here

Also, given the sample sql to create table and work with different data:

IF OBJECT_ID('FactTransaction') IS NOT NULL
    DROP TABLE FactTransaction
GO

CREATE TABLE FactTransaction (ShopId INT, TransactionDateKey INT, UserId INT, Amount INT)
GO

IF OBJECT_ID('DimDate') IS NOT NULL
    DROP TABLE DimDate
GO
CREATE TABLE DimDate(DateKey INT, FullDate DATE)
GO

IF OBJECT_ID('DimUser') IS NOT NULL
    DROP TABLE DimUser
GO
CREATE TABLE DimUser(UserId INT, UserName VARCHAR(50))
GO

IF OBJECT_ID('DimShop') IS NOT NULL
    DROP TABLE DimShop
GO
CREATE TABLE DimShop(ShopId INT, ShopName VARCHAR(50))
GO

--Shop 1
INSERT INTO FactTransaction values(1, 20210101, 1, 10)
INSERT INTO FactTransaction values(1, 20210101, 2, 5)
INSERT INTO FactTransaction values(1, 20210101, 3, 20)

INSERT INTO FactTransaction values(1, 20210102, 2, 10)
INSERT INTO FactTransaction values(1, 20210102, 4, 15)

INSERT INTO FactTransaction values(1, 20210103, 3, 5)
INSERT INTO FactTransaction values(1, 20210103, 4, 10)
INSERT INTO FactTransaction values(1, 20210103, 5, 20)
INSERT INTO FactTransaction values(1, 20210103, 1, 20)


--Shop 2
INSERT INTO FactTransaction values(2, 20210103, 2, 10)
INSERT INTO FactTransaction values(2, 20210103, 2, 5)
INSERT INTO FactTransaction values(2, 20210103, 2, 20)
GO

INSERT INTO DimDate VALUES(20210101, '2021-01-01')
INSERT INTO DimDate VALUES(20210102, '2021-01-02')
INSERT INTO DimDate VALUES(20210103, '2021-01-03')
GO

INSERT INTO DimUser VALUES(1, 'First')
INSERT INTO DimUser VALUES(2, 'Second')
INSERT INTO DimUser VALUES(3, 'Third')
INSERT INTO DimUser VALUES(4, 'Fourth')
INSERT INTO DimUser VALUES(5, 'Fifth')

GO

INSERT INTO DimShop VALUES(1, 'Shop 1')
INSERT INTO DimShop VALUES(2, 'Shop 2')
GO

Upvotes: 1

Related Questions