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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

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

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors