Forum Discussion

jcawley's avatar
jcawley
Helper III
5 years ago
Solved

Variable Concatenation for Key?

Good afternoon all,

So I'm trying to create a calculated column on my Appointments table that looks at a Sales table to determine if a sale linked with an appointment occured. 

I have two tables:

Appointment Table

Patient_ID

Appt_Date

Appointment had Sale?

12345

1/1/2021

 

23456

1/5/2021

 

34567

1/10/2021

 

45678

1/20/2021

 

 

Sales Table

Patient_IDPurchase_DateHA InvoiceConcatenation Key = Sales[Patient_ID]&Sales[Purchase_Date]
123451/1/2021TRUE1234544197
234561/6/2021TRUE2345644202
345671/15/2021FALSE3456744211
456781/20/2021FALSE4567844216

 

Now the problem I am running into, is that the Appointments[Appt_Date] does not always match Sales[Purchase_Date]. If the Sale[Purchase_Date] is within 3 days of the Appointments[Appt_Date], it should be associated with that Appt Date.

My first thought was:

Appointment had Sale? =
IF (
    LOOKUPVALUE (
        Sales[HA Invoice],
        Sales[Concatenation Key],
            Appointments[Patient ID] & ( Appointments[Appt Date]
                || Appointments[Appt Date] + 1
                || Appointments[Appt Date] + 2
                || Appointments[Appt Date] + 3
                || Appointments[Appt Date] - 1
                || Appointments[Appt Date] - 2
                || Appointments[Appt Date] - 3 )
    ) = TRUE,
    "True",
    "False"
)


But this doesn't work. All cells read "False".

Any help would be appreciated!

Need Help
  • v-alq-msft's avatar
    v-alq-msft
    5 years ago

    Hi, jcawley 

     

    Based on your description, i created data to reproduce your scenairo. The pbix file is attached in the end,

    Appointment:

     

    Sales:

     

    You may create a calculated column or a measure as below.

    Calculated column:

    Column = 
var c = 
COALESCE(
    COUNTROWS(
        FILTER(
            ALL(Sales),
            [Patient_ID]=EARLIER(Appointment[Patient_ID])&&
            ABS([Purchase_Date]-[Appt_Date])<=3
        )
    ),0
)
return
IF(
    c=0,
    FALSE(),
    TRUE()
)

     

    Measure:

    Measure = 
var c = 
COALESCE(
    COUNTROWS(
        FILTER(
            ALL(Sales),
            [Patient_ID]=MAX(Appointment[Patient_ID])&&
            ABS([Purchase_Date]-MAX(Appointment[Appt_Date]))<=3
        )
    ),0
)
return
IF(
    c=0,
    FALSE(),
    TRUE()
)

     

    Result:

     

    Best Regards

    Allan

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

6 Replies

  • selimovd's avatar
    selimovd
    Most Valuable Professional
    5 years ago

    Hey jcawley ,

     

    why don't you check if the DATEDIFF between the two dates is between -3 and 3 days?

    If you show me how the tables look like I can try to help you with the formula.

     

    If you need any help please let me know.
    If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍
     
    Best regards
    Denis
     
    • jcawley's avatar
      jcawley
      Helper III
      5 years ago

      I'll look into DATEDIFF, but the tables are above! You should be able to see them in my original post. 

    • jcawley's avatar
      jcawley
      Helper III
      5 years ago

      Hey selimovd , I couldn't get DATEDIFF to work in this context! Any other ideas?

      • selimovd's avatar
        selimovd
        Most Valuable Professional
        5 years ago

        Hey jcawley ,

         

        try the following code:

        Appointment had Sale =
VAR vPatientID = Appointments[Patient_ID]
VAR vDate = Appointments[Appt_Date]
RETURN
    CALCULATE(
        COUNTROWS( Sales ),
        FILTER(
            Sales,
            Sales[Patient_ID] = vPatientID
                && DATEDIFF( Sales[Purchase_Date], vDate, DAY ) <= 3
                && DATEDIFF( vDate, Sales[Purchase_Date], DAY ) >= -3
        )
    )

         

        If you need any help please let me know.
        If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍
         
        Best regards
        Denis
         

         

  • v-alq-msft's avatar
    v-alq-msft
    Community Support
    5 years ago

    Hi, jcawley 

     

    Based on your description, i created data to reproduce your scenairo. The pbix file is attached in the end,

    Appointment:

     

    Sales:

     

    You may create a calculated column or a measure as below.

    Calculated column:

    Column = 
var c = 
COALESCE(
    COUNTROWS(
        FILTER(
            ALL(Sales),
            [Patient_ID]=EARLIER(Appointment[Patient_ID])&&
            ABS([Purchase_Date]-[Appt_Date])<=3
        )
    ),0
)
return
IF(
    c=0,
    FALSE(),
    TRUE()
)

     

    Measure:

    Measure = 
var c = 
COALESCE(
    COUNTROWS(
        FILTER(
            ALL(Sales),
            [Patient_ID]=MAX(Appointment[Patient_ID])&&
            ABS([Purchase_Date]-MAX(Appointment[Appt_Date]))<=3
        )
    ),0
)
return
IF(
    c=0,
    FALSE(),
    TRUE()
)

     

    Result:

     

    Best Regards

    Allan

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.