Dax to Add column to check / lookup customer id based on the date b/w two dates from different table

[Rathod]

I have two tables Customer_Master and Campaign_Master, i want to add one column in Campaign_master to check the customer placed order based on the date compared with Start Date and End Date from Customer_Master Table.

I'm trying with below DAX but no luck, 

check_date =

VAR lookupcustomer = LOOKUPVALUE('Coupon Master'[Phone],[phone],[Mobile Number])

VAR checkdate = IF(lookupcustomer = BLANK(),"Number_Found", "Number not found")

RETURN

    IF(checkdate = "Number_Found",

        IF('WhatsApp Data'[Campaign Date] >= SELECTEDVALUE('Coupon Master'[Campaign Start Date]) &&

    'WhatsApp Data'[Campaign Date] <= SELECTEDVALUE('Coupon Master'[Campaign End Date]),

    "Placed ,

    "No"),"No")

Tables Campaign_Master

Mobile Number Campaign Date
8540984828 Thursday, 7 December, 2023
9620017476 Thursday, 7 December, 2023
9145765446 Thursday, 7 December, 2023
9550361092 Thursday, 7 December, 2023
9657009762 Thursday, 7 December, 2023
9741112378 Thursday, 7 December, 2023
7829912252 Thursday, 7 December, 2023
9717597598 Thursday, 7 December, 2023
8460849757 Thursday, 7 December, 2023
9590849273 Thursday, 7 December, 2023
8879033643 Wednesday, 6 December, 2023
8540984828 Wednesday, 6 December, 2023

Table Customer Master

Phone Campaign Start Date Campaign End Date
8460849757 15-Nov-23 31-Dec-23
9717597598 15-Nov-23 31-Dec-23
9657009762 15-Nov-23 31-Dec-23
8879169769 15-Nov-23 31-Dec-23
9145765446 15-Nov-23 31-Dec-23
7506500037 15-Nov-23 31-Dec-23
9657009762 15-Nov-23 31-Dec-23
9741112378 15-Nov-23 31-Dec-23
7829912252 15-Nov-23 31-Dec-23
9717597598 15-Nov-23 31-Dec-23
9923060217 15-Nov-23 31-Dec-23
9742897724 15-Nov-23 31-Dec-23
9945020221 15-Nov-23 31-Dec-23
8105200666 15-Nov-23 31-Dec-23
9036116601 15-Nov-23 31-Dec-23

Output in Campaign_Master Table

Mobile Number Campaign Date O/P
8540984828 Thursday, 7 December, 2023 NO
9620017476 Thursday, 7 December, 2023 No
9145765446 Thursday, 7 December, 2023 Placed
9550361092 Thursday, 7 December, 2023 No
9657009762 Thursday, 7 December, 2023 Placed
9741112378 Thursday, 7 March, 2024 No
7829912252 Thursday, 7 December, 2023 Placed
9717597598 Thursday, 7 December, 2023 Placed
8460849757 Thursday, 7 December, 2023 Placed
9590849273 Thursday, 7 December, 2023 No
8879033643 Wednesday, 6 December, 2023 No
8540984828 Wednesday, 6 December, 2023 No

[Jihwan_Kim · Accepted solution]

Hi,

I am not sure how your semantic model looks like, but please check the below picture and the attached pbix file if it suits your requirement.

Expected result CC =
VAR _condition =
    COUNTROWS (
        FILTER (
            Customer_Master,
            Customer_Master[Phone] = Campaign_Master[Mobile Number]
                && Customer_Master[Campaign Start Date] <= Campaign_Master[Campaign Date]
                && Customer_Master[Campaign End Date] >= Campaign_Master[Campaign Date]
        )
    ) <> 0
RETURN
    IF ( _condition, "Placed", "No" )

[Ahmedx · Accepted solution]

pls try this

[Jihwan_Kim · Accepted solution]

Hi,

I am not sure how your semantic model looks like, but please check the below picture and the attached pbix file if it suits your requirement.

Expected result CC =
VAR _condition =
    COUNTROWS (
        FILTER (
            Customer_Master,
            Customer_Master[Phone] = Campaign_Master[Mobile Number]
                && Customer_Master[Campaign Start Date] <= Campaign_Master[Campaign Date]
                && Customer_Master[Campaign End Date] >= Campaign_Master[Campaign Date]
        )
    ) <> 0
RETURN
    IF ( _condition, "Placed", "No" )