Forum Discussion
RogerSteinberg
Post Patron
Post PatronHow 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 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.
RogerSteinbergPost Patron
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
Solution Sage
Solution SageHi 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