Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
Rathod
Frequent Visitor

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

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

2 ACCEPTED SOLUTIONS
Jihwan_Kim
Super User
Super User

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.

 

Jihwan_Kim_0-1702100948389.png

 

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

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.


Go to My LinkedIn Page


View solution in original post

Ahmedx
Super User
Super User

2 REPLIES 2
Ahmedx
Super User
Super User

pls try this

Screenshot_1.png

Jihwan_Kim
Super User
Super User

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.

 

Jihwan_Kim_0-1702100948389.png

 

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

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.


Go to My LinkedIn Page


Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.