Need to calculate user count who purchased a particular product the first time

[UpharAnand]

Hi,

I want to create a dax which will calculate usercount where the users have purchased a particular product the first time and never purchased any product before that.

Example: User "123" have purchased a product with product id "xyz" the first time and never purchased any other product before that.

Please help

[UpharAnand · Accepted solution]

Thanks for your help. But your approach is too long . I applied simple logic and it worked. I used Rank function and it worked in only 2 lines of code.

Users who purchased first time =

CALCULATE

(

    DISTINCTCOUNT('Order Line Items'[uuid]),'Order Line Items'[first] = 1

    )

where 

first = IF

(

    'Order Line Items'[Rank] = 1

    ,'Order Line Items'[item_sku] = "2200837",1,0)

UsersSecond time = CALCULATE

(

    DISTINCTCOUNT('Order Line Items'[uuid]),

     'Order Line Items'[Second] = 1

     )

where 

Second = IF

(

    'Order Line Items'[Rank] >=1  

    ,'Order Line Items'[item_sku] = "2200837",1,0

    )

Rank is calculated column

Rank = RANKX

(

    FILTER('Order Line Items','Order Line Items'[uuid] = EARLIER('Order Line Items'[uuid])

,Line Items'[order date],,ASC,Dense)

[Bibiano_Geraldo]

Hi @UpharAnand ,

Please try the bellow DAX to create a calculated column:

User = 
VAR CurrentCustomer = 'Order Line Items'[uuid]

VAR InitialPurchaseDate = 
    CALCULATE(
        MIN('Order Line Items'[order date]),
        KEEPFILTERS('Order Line Items'[uuid] = CurrentCustomer)
    )

VAR TargetSKU = "2200837"

VAR InitialPurchaseSKU = 
    CALCULATE(
        FIRSTNONBLANK('Order Line Items'[item_sku], 1),
        FILTER(
            'Order Line Items',
            'Order Line Items'[uuid] = CurrentCustomer &&
            'Order Line Items'[order date] = InitialPurchaseDate
        )
    )

VAR HasTargetSKU = 
    CALCULATE(
        COUNTROWS('Order Line Items'),
        FILTER(
            'Order Line Items',
            'Order Line Items'[uuid] = CurrentCustomer &&
            'Order Line Items'[item_sku] = TargetSKU
        )
    ) > 0

RETURN
SWITCH(
    TRUE(),
    InitialPurchaseSKU <> TargetSKU && HasTargetSKU, 1,
    0
)

[Anonymous]

Hi @UpharAnand ,
Here is my sample data
User

User	Name
1001	Alan
1002	Bob
1003	Claire
1004	David
1005	Elf

Product

Product ID	Product
101	ABC
102	EFG
103	HIJ
104	KLM
105	OPQ
106	RST
107	UVW
108	XYZ

Fact

User Name	Product ID	Purchase Date
1001	101	11/1/2024
1001	108	11/2/2024
1001	106	11/3/2024
1002	108	11/4/2024
1002	101	11/5/2024
1002	104	11/6/2024
1003	103	11/7/2024
1003	104	11/8/2024
1003	102	11/9/2024
1004	108	11/10/2024
1004	105	11/11/2024
1004	108	11/12/2024
1005	107	11/13/2024
1005	102	11/14/2024
1005	101	11/15/2024

Create a calculate column

Total user1 = 
VAR _firstPurchase = 
CALCULATE(
    MIN('Fact'[Purchase Date]),
    ALLEXCEPT(
        'Fact',
        'Fact'[User ID])
)
VAR _ProductId = 
CALCULATE(
    MAX('Fact'[Product ID]),
    FILTER(
        ALL('Fact'),
        RELATED('Product'[Product]) = "XYZ"
    )
)
RETURN
IF(
    'Fact'[Product ID] = _ProductId && 'Fact'[Purchase Date] = _firstPurchase,
    1,
   

Final output

Best regards,
Albert He

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[UpharAnand]

Omg, you are god to me. 

I just got one more scenario.

I want usercount who have purchased product with productId = "xyz" the first time including all the previously purchased product.

Example: 

1. Suppose UserID= 1001 have purchased the product with id = "xyz" the first time plus he has also     

 purchased some other product in the past with other product ids.

2. UserID= 1002 have purchased the product with id = "xyz" the first time But in the past he has not purchased any product with different ids.

SO here the count will be 1 which is userid1001 because in the past He has purchased some other products

[Anonymous]

Hi @UpharAnand ,
Based on your description, the sample data is as follows

User ID	Product ID	Purchase Date
1001	101	11/1/2024
1001	108	11/2/2024
1001	106	11/3/2024
1002	108	11/4/2024
1002	108	11/5/2024
1002	108	11/6/2024
1003	103	11/7/2024
1003	104	11/8/2024
1003	102	11/9/2024
1004	108	11/10/2024
1004	105	11/11/2024
1004	108	11/12/2024
1005	107	11/13/2024
1005	102	11/14/2024
1005	101	11/15/2024

Change the code as

Total user = 
VAR _firstPurchase = 
CALCULATE(
    MIN('Fact'[Purchase Date]),
    ALLEXCEPT(
        'Fact',
        'Fact'[User ID])
)
VAR _ProductId = 
CALCULATE(
    MAX('Fact'[Product ID]),
    FILTER(
        ALL('Fact'),
        RELATED('Product'[Product]) = "XYZ"
    )
)
VAR _different = 
CALCULATE(
    DISTINCTCOUNT('Fact'[Product ID]),
    ALLEXCEPT(
        'Fact',
        'Fact'[User ID]
    )
)

RETURN
IF(
    'Fact'[Product ID] = _ProductId && 'Fact'[Purchase Date] = _firstPurchase && _different > 1,
    1,
    0
)

User ID = 102, but only one id. User ID = 104, 2 distinct id

Final output

 

Best regards,
Albert He

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[UpharAnand]

Actually NO. We will only consider User ID: 1001

Reason:

User ID 1001 has purchased 108 on 11/2/2024(this product id is second order for him as He has already purchased 101 on 11/1/2024)

User ID 1004 will not be considered as he has purchased 108 on 11/10/2024 but before that he didnt purchased any product.

IN MY SCENARIO WE WILL ONLY CONSIDER THOSE USERS WHO HAVE PURCHASED PRODUCT ID 108 FIRST TIME AND THEY HAVE ALSO  ALREADY PURCHASED SOME OTHER PRODUCT ID IN THE PAST.

SO I HAVE 2 FILTER CRITERIA : CHECK WHETHER USER HAS PURCHASED PRODUCT ID 108 OR NOT , IF YES THEN AGAIN CHECK THEY HAVE PURCHASED SOME OTHER PRODUCTS IN THE PAST OR NOT.

IF BOTH THE CRITERIA MATCH THEN ONLY WE WILL CONSIDER THEM

[Anonymous]

Hi @UpharAnand ,
You can try this
Create a column

User = 
VAR CurrentUserID = [User ID]
VAR _firstPurchaseDate = 
CALCULATE(
    MIN('Fact'[Purchase Date]),
    ALLEXCEPT(
        'Fact',
        'Fact'[User ID])
)
VAR _ProductId = 
CALCULATE(
    MAX('Fact'[Product ID]),
    FILTER(
        ALL('Fact'),
        RELATED('Product'[Product]) = "XYZ"
    )
)
VAR _firstPurchaseID = 
CALCULATE(
    MAX('Fact'[Product ID]),
    FILTER(
        ALLEXCEPT(
            'Fact',
            'Fact'[User ID]
        ),
        'Fact'[Purchase Date] = _firstPurchaseDate
    )
)
VAR _contains = 
IF(
    CALCULATE(
        COUNTROWS('Fact'),
        'Fact'[User ID] = CurrentUserID,
        'Fact'[Product ID] = _ProductId
    ) > 0,
    1,
    0
)
RETURN
IF(
    _firstPurchaseID <> _ProductId && _contains = 1,
    1,
    0
)

Create a measure

Result = 
CALCULATE(
    DISTINCTCOUNT('Fact'[User ID]),
    'Fact'[User] = 1 
)

Final output

Best regards,
Albert He

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[UpharAnand]

User =

var Currentuser ='Order Line Items'[uuid]

var firstorderdate = CALCULATE(MIN('Order Line Items'[order date]),

ALLEXCEPT('Order Line Items','Order Line Items'[uuid])

)

VAR RequiredSKU =

CALCULATE(MAX('Order Line Items'[item_sku]),

FILTER(

    ALL('Order Line Items'),

    'Order Line Items'[item_sku] = "2200837")

)

VAR firstsku =

CALCULATE(MAX('Order Line Items'[item_sku]),

FILTER(ALLEXCEPT('Order Line Items','Order Line Items'[uuid]),

'Order Line Items'[order date] = firstorderdate

))

var _contains =

IF(CALCULATE(COUNTROWS('Order Line Items'),

'Order Line Items'[uuid] = Currentuser,

'Order Line Items'[item_sku] = RequiredSKU

)>0,1,0)

RETURN

IF(

    firstsku <> RequiredSKU &&

    _contains = 1,1,0).

Its showing circular dependency error message
"A circular dependency was detected: Order Line Items[Column], Order Line Items[Check], Order Line Items[Column]."

I am sorry but I am too low in power BI and just got this hard situation 😞

[Bibiano_Geraldo]

Hi @UpharAnand ,

1- Ensure there is a relationship between the two tables: The User Table should be linked to the Product Table using the UserID column. If there isn't a direct relationship, use a bridge table.

2- Assume that UserTable has UserID column and ProductTable has ProductID and PurchaseDate columns, create a measure by this DAX:

FirstTimeProductUsers = 
CALCULATE(
    DISTINCTCOUNT('UserTable'[UserID]),  -- Count distinct users who satisfy the conditions
    FILTER(
        'UserTable',  -- Iterate through each user
        CALCULATE(
            MIN('ProductTable'[PurchaseDate])  -- Find the user's earliest purchase date
        ) = CALCULATE(
            MINX(
                FILTER('ProductTable', 'ProductTable'[ProductID] = "xyz"),  -- Find the earliest purchase date for the specific product 'xyz'
                'ProductTable'[PurchaseDate]
            )
        ) &&
        CALCULATE(COUNTROWS('ProductTable')) = 1  -- Ensure the user has only one purchase record in the ProductTable
    )
)

The goal is to count users who purchased a specific product (e.g., product "xyz") as their first and only purchase. Here's how the measure works step by step:

1. Count Unique Users: The DISTINCTCOUNT function counts each unique user ID who meets the specified conditions.

2. Filter Each User: The FILTER function goes through each user in the UserTable and checks:

   • When did the user make their first purchase? (MIN('ProductTable'[PurchaseDate]) calculates the earliest purchase date for each user).
   • Did this first purchase match the specific product (ProductID = "xyz")? The MINX function finds the earliest date the user purchased "xyz".
   • This ensures that only users whose first-ever purchase was "xyz" are included.

3. Exclude Users with Other Purchases: The COUNTROWS function checks if the user has only one row in the ProductTable. If a user has purchased other products, they are excluded from the count.

4. Return the Result: The measure calculates how many users meet both criteria:

   • They purchased "xyz" as their first product.
   • They didn't purchase anything else.

[Bibiano_Geraldo]

Hi @UpharAnand , did you tried this solution?

[UpharAnand]

Yes, but its showing wrong data

[Bibiano_Geraldo]

To be more assertive, can you share a sample with no sensitive data? 

[UpharAnand]

@Anonymous @Bibiano_Geraldo 

https://drive.google.com/file/d/1b8mRJtR1kgTZobMv2BTNBfeLkDgZvYKd/view?usp=drivesdk.

Please use the attached file as sample.

Whenever l am using Excel as data source then l am not getting this circular dependency error.But when l use SQL as data source then only I am getting the error

[FarhanJeelani]

Hi @UpharAnand , 

Assuming:

1. Your table is named Sales.
2. The table contains the following relevant columns:
   • UserID (unique identifier for the user)
   • ProductID (identifier for the product purchased)
   • PurchaseDate (date of the purchase)

FirstTimeBuyersYesterday =
VAR YesterdayDate = TODAY() - 1

-- Step 1: Get the first purchase date for each user
VAR FirstPurchaseTable =
    ADDCOLUMNS(
        SUMMARIZE(Sales, Sales[UserID]),
        "FirstPurchaseDate", MIN(Sales[PurchaseDate])
    )

-- Step 2: Filter for users whose first purchase was yesterday
VAR UsersFirstTimeYesterday =
    FILTER(
        FirstPurchaseTable,
        [FirstPurchaseDate] = YesterdayDate
    )

-- Step 3: Check if the product purchased matches the target product
VAR TargetProduct = "xyz" -- Replace with the desired Product ID
VAR FirstTimeUsersTargetProduct =
    FILTER(
        Sales,
        Sales[UserID] IN SELECTCOLUMNS(UsersFirstTimeYesterday, "UserID", Sales[UserID]) &&
        Sales[ProductID] = TargetProduct &&
        Sales[PurchaseDate] = YesterdayDate
    )

-- Step 4: Count distinct users
RETURN COUNTROWS(SUMMARIZE(FirstTimeUsersTargetProduct, Sales[UserID]))

Please mark this as solution if it helps. Appreciate Kudos.

[UpharAnand]

Hi @FarhanJeelani , I am not asking for yesterday. I am just asking for count of users who has purchased product with id "xyz" first time and never ever purchased any product before that 

[shafiz_p]

Hi @UpharAnand 

You could try this:

UserCountFirstTimePurchase = 
CALCULATE(
    DISTINCTCOUNT(Sales[UserID]),
    FILTER(
        Sales,
        Sales[ProductID] = "xyz" &&
        CALCULATE(
            COUNTROWS(Sales),
            ALLEXCEPT(Sales, Sales[UserID])
        ) = 1
    )
)

Hope this helps!!

If this solved your problem, please accept it as a solution and a kudos!!

Best Regards,
Shahariar Hafiz

[UpharAnand]

@shafiz_p Can you please explain me this dax function?

My userid and product id both are coming from two different tables

[shafiz_p]

This above formula will count user who purchased product xyz or specific product for the first time and no other purchases.

Consideration:

Since your UserID and ProductID are in different tables, you need to use relationships between these tables. Let's assume you have two tables: Users and Purchases. Here's how you can adjust the DAX formula:

1. Users Table: Contains UserID.
2. Purchases Table: Contains UserID and ProductID.

Version 1:

To count users who purchased each product for the first time and had no other purchases, we need to ensure the DAX formula checks that the user has only one purchase record in total, and that record is for the specific product. Here's an adjusted DAX formula to achieve this:

UserCountFirstTimePurchase = 
SUMX(
    VALUES(Purchases[ProductID]),
    CALCULATE(
        DISTINCTCOUNT(Users[UserID]),
        FILTER(
            Purchases,
            Purchases[ProductID] = EARLIER(Purchases[ProductID]) &&
            CALCULATE(
                COUNTROWS(Purchases),
                ALLEXCEPT(Purchases, Purchases[UserID])
            ) = 1
        )
    )
)

Explanation

• SUMX: Iterates over a table and evaluates an expression for each row.
• VALUES(Purchases[ProductID]): Returns a table of distinct product IDs.
• EARLIER(Purchases[ProductID]): Refers to the current row context of ProductID in the iteration.
• DISTINCTCOUNT(Users[UserID]): Counts distinct users from the Users table.
• FILTER(...): Applies the filter to the Purchases table to ensure the user has only one purchase record for the current product.

Version 2:

To count users who purchased a specific product for the first time and had no prior purchases of any other product, but could have made purchases afterward, we need to adjust the DAX formula accordingly. Here's how you can do it:

UserCountFirstTimePurchase = 
CALCULATE(
    DISTINCTCOUNT(Users[UserID]),
    FILTER(
        Purchases,
        Purchases[ProductID] = EARLIER(Purchases[ProductID]) &&
        CALCULATE(
            MINX(
                FILTER(Purchases, Purchases[UserID] = EARLIER(Purchases[UserID])),
                Purchases[PurchaseDate]
            )
        ) = Purchases[PurchaseDate]
    )
)

Note : To get the exact solution it is highly suggested to provide representative data and expected output.

Hope this helps!!

If this solved your problem, please accept it as a solution and a kudos!!

Best Regards,
Shahariar Hafiz