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

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
Anonymous
Not applicable

Frequent callers to call center

Hi 

I have data where it has phone numbers, callID, Date like below, I would like to add new column if phone numbers are same and call Id is different and date is similar I would like to tag as called on same day , if phone number is same and call ID is different and date is diffent i would like to tag as per teh date difference called after 1day or 2days, if the phone numbers are same and call ID is same I dnt want to tag them.

phone numberCallID       Date                         New Column
123-456-789                      ABC       15-sep-2022 Called on same day
123-456-789  ABC     15-sep-2022  
123-456-789     XYZ   15-sep-2022   Called on same day
123-456-789       GHC       16-sep-2022  Called on next day
789-123-456    JKU18-sep-2022 
789-123-456   YOU  20-sep-2022called after 2days
456-789-123HBJ20-sep-2022non repeat
456-789-123HBJ20-sep-2022non repeat

           Please help me on this
       

1 ACCEPTED SOLUTION
v-easonf-msft
Community Support
Community Support

Hi, @Anonymous 

You need to sort the table and add an index column in PQ like:

veasonfmsft_0-1663325717527.png

Then  try calculated column like:

Index1 = RANKX(FILTER(ALL('Table'),'Table'[phone number]=EARLIER('Table'[phone number])),'Table'[Index],,ASC,Dense)
Result =
VAR _mindate =
    CALCULATE (
        MAX ( 'Table'[Date] ),
        FILTER ( ALLEXCEPT ( 'Table', 'Table'[phone number] ), 'Table'[Index1] = 1 )
    )
VAR _datediff =
    DATEDIFF ( _mindate, 'Table'[Date], DAY )
RETURN
    SWITCH (
        TRUE (),
        'Table'[Index1] = 1, BLANK (),
        _datediff = 0, "Called on same day",
        _datediff = 1, "Called on next day",
        "Called after " & _datediff & " days"
    )

veasonfmsft_1-1663325881263.png

Best Regards,
Community Support Team _ Eason

View solution in original post

2 REPLIES 2
Anonymous
Not applicable

Thank you so much! It worked 🙂

v-easonf-msft
Community Support
Community Support

Hi, @Anonymous 

You need to sort the table and add an index column in PQ like:

veasonfmsft_0-1663325717527.png

Then  try calculated column like:

Index1 = RANKX(FILTER(ALL('Table'),'Table'[phone number]=EARLIER('Table'[phone number])),'Table'[Index],,ASC,Dense)
Result =
VAR _mindate =
    CALCULATE (
        MAX ( 'Table'[Date] ),
        FILTER ( ALLEXCEPT ( 'Table', 'Table'[phone number] ), 'Table'[Index1] = 1 )
    )
VAR _datediff =
    DATEDIFF ( _mindate, 'Table'[Date], DAY )
RETURN
    SWITCH (
        TRUE (),
        'Table'[Index1] = 1, BLANK (),
        _datediff = 0, "Called on same day",
        _datediff = 1, "Called on next day",
        "Called after " & _datediff & " days"
    )

veasonfmsft_1-1663325881263.png

Best Regards,
Community Support Team _ Eason

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

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

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.