Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi,
I have a table in below format and want to see the first call of the day from the same number.
Please note there can be more than one call from "blocked number" at same time anwered by different agents. In that case sort by agent name
Called DateTime | Called from | Agent | Expected result - First call of the day from same Number |
11/01/2019 11:21:20 | 123 | A | True |
11/01/2019 11:30:20 | 123 | B | False |
11/02/2019 10:20:10 | 123 | A | True |
11/01/2019 13:21:20 | 456 | C | True |
11/03/2019 14:20:10 | 456 | C | True |
11/04/2019 10:10:20 | Blocked | A | True |
11/04/2019 10:10:20 | Blocked | C | False |
Thanks,
Daven
Solved! Go to Solution.
Hi @Daven
Add this as a calculated column:
First Call =
VAR RowDateTime = Table1[Called DateTime]
VAR RowDate = TRUNC ( Table1[Called DateTime] )
VAR RowCalledFrom = Table1[Called from]
VAR RowAgent = Table1[Agent ]
VAR FirstCallTime =
CALCULATE (
MIN ( Table1[Called DateTime] ),
FILTER (
ALL ( Table1 ),
TRUNC ( Table1[Called DateTime] ) = RowDate
&& Table1[Called from] = RowCalledFrom
)
)
VAR FirstAgent =
CALCULATE (
MIN ( Table1[Agent ] ),
FILTER (
ALL ( Table1 ),
Table1[Called DateTime] = RowDateTime
&& Table1[Called from] = RowCalledFrom
)
)
VAR Result =
SWITCH (
TRUE(),
RowCalledFrom <> "Blocked" && RowDateTime = FirstCallTime, TRUE(),
RowCalledFrom = "Blocked" && RowDateTime = FirstCallTime && RowAgent = FirstAgent, TRUE(),
FALSE()
)
RETURN Result
Best regards,
Martyn
If I answered your question, please help others by accepting it as a solution.
Hi,
Yes the row 3 should be true. Thanks for catching it.
I updated it.
Daven
Hi @Daven
Add this as a calculated column:
First Call =
VAR RowDateTime = Table1[Called DateTime]
VAR RowDate = TRUNC ( Table1[Called DateTime] )
VAR RowCalledFrom = Table1[Called from]
VAR RowAgent = Table1[Agent ]
VAR FirstCallTime =
CALCULATE (
MIN ( Table1[Called DateTime] ),
FILTER (
ALL ( Table1 ),
TRUNC ( Table1[Called DateTime] ) = RowDate
&& Table1[Called from] = RowCalledFrom
)
)
VAR FirstAgent =
CALCULATE (
MIN ( Table1[Agent ] ),
FILTER (
ALL ( Table1 ),
Table1[Called DateTime] = RowDateTime
&& Table1[Called from] = RowCalledFrom
)
)
VAR Result =
SWITCH (
TRUE(),
RowCalledFrom <> "Blocked" && RowDateTime = FirstCallTime, TRUE(),
RowCalledFrom = "Blocked" && RowDateTime = FirstCallTime && RowAgent = FirstAgent, TRUE(),
FALSE()
)
RETURN Result
Best regards,
Martyn
If I answered your question, please help others by accepting it as a solution.
Hi Martyn,
Thank you so much for your time and providing the solution. It works!
Hope you have a great day!
Best,
Daven
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
100 | |
75 | |
73 | |
49 |
User | Count |
---|---|
145 | |
109 | |
109 | |
90 | |
64 |