Forum Discussion
Post PatronHow to dynamically select purchases based on a date slicer where the purchase is the first per user
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.
Solution SageHi 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.
Post PatronIncredible ! 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
)
)
- AnkitBI
Hi RogerSteinberg Try below
return CALCULATE( [total sales], filter(test,test[user_id] in EXCEPT( customers, priorcustomers ) ))- RogerSteinberg
It stays unfiltered
- RogerSteinberg
Surprisingly, it worked when I replaced the date column from my date table with the one of my fact table