Forum Discussion

RogerSteinberg's avatar
RogerSteinberg
Icon for Post Patron rankPost Patron
6 years ago
Solved

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 user...
Need Help
  • AnkitBI's avatar
    AnkitBI
    6 years ago

    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.

     

RogerSteinberg's avatar
RogerSteinberg
Icon for Post Patron rankPost Patron
6 years ago

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
    )
)

 

 

  • AnkitBI's avatar
    AnkitBI
    Icon for Solution Sage rankSolution Sage
    6 years ago

    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.

     

    • RogerSteinberg's avatar
      RogerSteinberg
      Icon for Post Patron rankPost Patron
      6 years ago

      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
    )
)

       

      • AnkitBI's avatar
        AnkitBI
        Icon for Solution Sage rankSolution Sage
        6 years ago

        Hi RogerSteinberg Try below

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