Variable Concatenation for Key?

[jcawley]

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_ID	Purchase_Date	HA Invoice	Concatenation Key = Sales[Patient_ID]&Sales[Purchase_Date]
12345	1/1/2021	TRUE	1234544197
23456	1/6/2021	TRUE	2345644202
34567	1/15/2021	FALSE	3456744211
45678	1/20/2021	FALSE	4567844216

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!

[Ashish_Mathur]

Hi,

Show the exact result you are expecting.

[v-alq-msft · Accepted solution]

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.

[selimovd]

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

Blog: WhatTheFact.bi

[jcawley]

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

[selimovd]

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

Blog: WhatTheFact.bi

[jcawley]

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