Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

If based on first date occurrence

Working with Pharmaceutical data.  Trying to determine the first occurrence of a prescription as "New" prescription and all successive orders of that prescription as "refills"   Each patient will h...
Need Help
  • AlB's avatar
    AlB
    7 years ago

    Anonymous

     

    Try this for the new column:

     

    NewColumn=
    VAR _StartDate =
CALCULATE (
    MIN ( Query1[Start date of service] ),
    ALLEXCEPT ( Query1, Query1[Product], Query1[Customer] )
)
    RETURN 
    IF( Query1[Start date of service] = _StartDate, "New", "Refill")

     

AlB's avatar
AlB
Community Champion
7 years ago

Hi Anonymous

 

I guess each  prescription has a unique identifier? Why don't you show a sample of your data model? It would make things much easier for people trying to help. 

You could use an ascending RANKX( ) based on date within each prescription  and assign a "Yes" to the row ranked number  1. Or you can also check whether the date for the current row has the MIN(date) for that prescription.