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 table...
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.

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