The ultimate Microsoft Fabric, Power BI, Azure AI, and SQL learning event! Join us in Stockholm, Sweden from September 24-27, 2024.
2-for-1 sale on June 20 only!
Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
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
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Check out the June 2024 Power BI update to learn about new features.
User | Count |
---|---|
106 | |
101 | |
82 | |
62 | |
55 |
User | Count |
---|---|
252 | |
119 | |
115 | |
95 | |
70 |