Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
RogerSteinberg
Post Patron
Post Patron

How to dynamically select purchases based on a date slicer where the purchase is the first per user

Hi,

 

I have a table full of Purchases per user, per date. The goal is to have a slicer where the user selects a date range and a visual (table/matrix) would display in one column the count of users who have purchased during and before that selected date range and another column for new purchasers (never purchased before that selected date range).

 

For example:

dateuser id
1-Jan5
2-Jan6
3-Jan7
4-Jan5
5-Jan6

 

If i have a date slicer that selects Jan 3r to Jan 5th, I should get this:

New UsersOld Users
12

That's because user id 7 never purchased before that selected range and user 5,6 have purchased before January 3rd.

 

What type of DAX measure can i do to achieve the new user column?

 

1 ACCEPTED SOLUTION

Hi @RogerSteinberg Please try below. You were missing ALL.

 

testing_testing = 
var min_date =
    CALCULATE(
        MIN(test[date]),
        ALLSELECTED(test[date])
    )

var customers =
    values(test[user_id])

var priorcustomers = 
    CALCULATETABLE(
        VALUES(test[user_id]),
        FILTER(
        all(test),
            test[date] < min_date
        )
    )
    
return
COUNTROWS(
    EXCEPT(
        customers,
        priorcustomers
    )
)

Thanks
Ankit Jain
Do Mark it as solution if the response resolved your problem. Do Kudo the response if it seems good and helpful.

 

View solution in original post

7 REPLIES 7
AnkitBI
Solution Sage
Solution Sage

Hi @RogerSteinberg - Check if below links are helpful

 

https://www.youtube.com/watch?v=GK-W25RM87Q

https://www.youtube.com/watch?v=h9kRwgamLcw

 

Thanks
Ankit Jain
Do Mark it as solution if the response resolved your problem. Do Kudo the response if it seems good and helpful.

 

Thank you for the documentation.

 

I followed the video's procedure, but im getting 3 instead of 1. Nothing is being filtered. ANy idea ?

2020-03-11 10_35_29-Untitled - Power BI Desktop.png

Measure:

 

testing_testing = 
var min_date =
    CALCULATE(
        MIN(test[date]),
        ALLSELECTED(test[date])
    )

var customers =
    values(test[user_id])

var priorcustomers = 
    CALCULATETABLE(
        VALUES(test[user_id]),
        FILTER(
            test,
            test[date] < min_date
        )
    )
    
return
COUNTROWS(
    EXCEPT(
        customers,
        priorcustomers
    )
)

 

 

Hi @RogerSteinberg Please try below. You were missing ALL.

 

testing_testing = 
var min_date =
    CALCULATE(
        MIN(test[date]),
        ALLSELECTED(test[date])
    )

var customers =
    values(test[user_id])

var priorcustomers = 
    CALCULATETABLE(
        VALUES(test[user_id]),
        FILTER(
        all(test),
            test[date] < min_date
        )
    )
    
return
COUNTROWS(
    EXCEPT(
        customers,
        priorcustomers
    )
)

Thanks
Ankit Jain
Do Mark it as solution if the response resolved your problem. Do Kudo the response if it seems good and helpful.

 

Incredible ! Thank you .
Quick little bonus. I tried to also create a measure to calculate sales based on this measure

So i changed the following code:

return
COUNTROWS(
    EXCEPT(
        customers,
        priorcustomers
    )
)

to this but i my $ are not being filtered by my "except" filter condition:

note: my total sales measure is sumx(table,revenue)

return
CALCULATE(
    [total sales],
    EXCEPT(
        customers,
        priorcustomers
    )
)

 

Hi @RogerSteinberg Try below

return
CALCULATE(
    [total sales],
    filter(test,test[user_id] in EXCEPT(
        customers,
        priorcustomers
    )
))

 

 

Surprisingly, it worked when I replaced the date column from my date table with the one of my fact table

 

It stays unfiltered

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.