Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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:
date | user id |
1-Jan | 5 |
2-Jan | 6 |
3-Jan | 7 |
4-Jan | 5 |
5-Jan | 6 |
If i have a date slicer that selects Jan 3r to Jan 5th, I should get this:
New Users | Old Users |
1 | 2 |
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?
Solved! Go to 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.
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 ?
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
User | Count |
---|---|
141 | |
113 | |
104 | |
78 | |
64 |
User | Count |
---|---|
136 | |
125 | |
107 | |
70 | |
61 |