Power BI is turning 10, and we’re marking the occasion with a special community challenge. Use your creativity to tell a story, uncover trends, or highlight something unexpected.
Get startedJoin us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.
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 number | CallID | 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 | JKU | 18-sep-2022 | |
789-123-456 | YOU | 20-sep-2022 | called after 2days |
456-789-123 | HBJ | 20-sep-2022 | non repeat |
456-789-123 | HBJ | 20-sep-2022 | non repeat |
Please help me on this
Solved! Go to Solution.
Hi, @Anonymous
You need to sort the table and add an index column in PQ like:
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"
)
Best Regards,
Community Support Team _ Eason
Thank you so much! It worked 🙂
Hi, @Anonymous
You need to sort the table and add an index column in PQ like:
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"
)
Best Regards,
Community Support Team _ Eason
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
Check out the June 2025 Power BI update to learn about new features.
User | Count |
---|---|
72 | |
68 | |
53 | |
39 | |
33 |
User | Count |
---|---|
70 | |
63 | |
57 | |
49 | |
46 |