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

     

itsmebvk's avatar
itsmebvk
Continued Contributor
7 years ago

Anonymousdid you try to tweak context as follow? 

 

Start Date = CALCULATE(MIN(Sheet1[Start]),ALLEXCEPT(Sheet1,Sheet1[Patient ID],Sheet1[Part Number]))
Anonymous's avatar
Anonymous
Not applicable
7 years ago

Thank You that works.

 

however I've an issue with the if statement.  PowerBI is only allowing me to type in measures to do the logical test.  not columns, am I doing something wrong?