topic Re: Return earliest non blank row by User ID in DAX Commands and Tips

Return earliest non blank row by User ID

JamesMoody — Mon, 27 Nov 2023 22:29:40 GMT

I am wanting to have a calculated column that returns '1' for the first 'User ID' against our 'Call IDs', the purpose is to determine the first agent that took one of our calls.

At the moment I have the below measure which is working as expected but the problem i have is that it returns a '1' for blank rows if that is the earliest user ID - I am trying to work out how this measure can be changed to show '1' for the first non blank row.

First Agent =

VAR Call_id = 'Call Records'[Call Record ID]

VAR Earliest_date = CALCULATE(MIN('Call Records'[Start DateTime]),

FILTER('Call Records','Call Records'[Call Record ID]=Call_id))

VAR Earliest_Agent = CALCULATE(MIN('Call Records'[User ID]),

FILTER('Call Records','Call Records'[Start DateTime]=Earliest_date),
FILTER('Call Records','Call Records'[Call Record ID]=Call_id))

RETURN IF('Call Records'[Start DateTime]=Earliest_date && 'Call Records'[User ID]=Earliest_Agent,
1,BLANK())

Here is a snapshot of the data

Call ID	User ID	Start DateTime	First Agent
013b5db2-f620-4fc1-9b94	7676b92c-c51c-4ee0-aba7	2023-11-27T12:35:56.0630000	1
013b5db2-f620-4fc1-9b94	7676b92c-c51c-4ee0-aba7	2023-11-27T12:35:57.1830000	1
013b5db2-f620-4fc1-9b94		2023-11-27T12:35:57.1870000
013b5db2-f620-4fc1-9b94		2023-11-27T12:36:30.5400000
013b5db2-f620-4fc1-9b94	b0e97635-769b-439b-bbf2	2023-11-27T12:36:30.5430000
017d0039-5b25-43af-acde	b0e97635-769b-439b-bbf2	2023-11-27T14:29:54.4900000	1
017d0039-5b25-43af-acde	b0e97635-769b-439b-bbf2	2023-11-27T14:29:55.6300000	1
017d0039-5b25-43af-acde		2023-11-27T14:29:55.6330000
03b5f72e-7199-474f-94df	9df610bf-d62e-4110-aad8	2023-11-27T09:45:52.8600000
03b5f72e-7199-474f-94df		2023-11-27T09:45:52.8500000	1
03b5f72e-7199-474f-94df	9df610bf-d62e-4110-aad8	2023-11-27T09:45:52.8600000
0b27d03a-cf61-450d-87aa		2023-11-24T10:45:42.3100000	1
0b27d03a-cf61-450d-87aa		2023-11-24T10:45:42.3230000	1

Thanks

Re: Return earliest non blank row by User ID

FreemanZ — Tue, 28 Nov 2023 01:25:34 GMT

hi JamesMoody ,

try to add a calculated column like:

column = VAR _startdatetime = MINX( FILTER( data, data[Call ID]=EARLIER(data[Call ID]) ), data[Start DateTime] ) VAR _result= IF( [User ID]<>BLANK() && _startdatetime = [Start Datetime], 1 ) RETURN _result

it worked like:

Re: Return earliest non blank row by User ID

JamesMoody — Tue, 28 Nov 2023 03:03:16 GMT

Hi FreemanZ,

This seems to have solved the issue of looking at the blank rows but there are now some rows without the '1' to indicate any first 'User ID'.

Example:

Here is another example from my data set that is also not showing anything

Call ID	User ID	Start DateTime	Column
fd114fd1-21e3-46a8-8c80	8f972230-ecc7-4413-b184	10/11/2023 9:24
fd114fd1-21e3-46a8-8c80		10/11/2023 9:23
fd114fd1-21e3-46a8-8c80		10/11/2023 9:23
fd114fd1-21e3-46a8-8c80		10/11/2023 9:23
fd114fd1-21e3-46a8-8c80	9df610bf-d62e-4110-aad8	10/11/2023 9:25
fd114fd1-21e3-46a8-8c80	8f972230-ecc7-4413-b184	10/11/2023 9:24
fd114fd1-21e3-46a8-8c80		10/11/2023 9:23
fd114fd1-21e3-46a8-8c80	8f972230-ecc7-4413-b184	10/11/2023 9:24
fd114fd1-21e3-46a8-8c80	0c446220-b40a-4022-bf18	10/11/2023 9:23
fd114fd1-21e3-46a8-8c80	8f972230-ecc7-4413-b184	10/11/2023 9:25
fd114fd1-21e3-46a8-8c80	8f972230-ecc7-4413-b184	10/11/2023 9:25
fd114fd1-21e3-46a8-8c80	9df610bf-d62e-4110-aad8	10/11/2023 9:27
fd114fd1-21e3-46a8-8c80	9df610bf-d62e-4110-aad8	10/11/2023 9:25

Re: Return earliest non blank row by User ID

FreemanZ — Tue, 28 Nov 2023 05:31:10 GMT

like this?

column = VAR _startdatetime = MINX( FILTER( data2, data2[Call ID]=EARLIER(data2[Call ID]) ), data2[Start DateTime] ) VAR _result= IF( [User ID]<>BLANK() && _startdatetime = [Start Datetime], 1 ) RETURN _result

Re: Return earliest non blank row by User ID

JamesMoody — Tue, 28 Nov 2023 20:25:34 GMT

Apologies, I had accidentally removed some characters from the IDs

Here is the correct data and how I am seeing it.

Data:

Call ID	User ID	Start DateTime
fd114fd1-21e3-46a8-8c80-eb57d6af18e8	8f972230-ecc7-4413-b184-60025d7398b7	10/11/2023 9:24
fd114fd1-21e3-46a8-8c80-eb57d6af18e8		10/11/2023 9:23
fd114fd1-21e3-46a8-8c80-eb57d6af18e8		10/11/2023 9:23
fd114fd1-21e3-46a8-8c80-eb57d6af18e8		10/11/2023 9:23
fd114fd1-21e3-46a8-8c80-eb57d6af18e8	9df610bf-d62e-4110-aad8-5010f3699594	10/11/2023 9:25
fd114fd1-21e3-46a8-8c80-eb57d6af18e8	8f972230-ecc7-4413-b184-60025d7398b7	10/11/2023 9:24
fd114fd1-21e3-46a8-8c80-eb57d6af18e8		10/11/2023 9:23
fd114fd1-21e3-46a8-8c80-eb57d6af18e8	8f972230-ecc7-4413-b184-60025d7398b7	10/11/2023 9:24
fd114fd1-21e3-46a8-8c80-eb57d6af18e8	0c446220-b40a-4022-bf18-7ae5a11fcd23	10/11/2023 9:23
fd114fd1-21e3-46a8-8c80-eb57d6af18e8	8f972230-ecc7-4413-b184-60025d7398b7	10/11/2023 9:25
fd114fd1-21e3-46a8-8c80-eb57d6af18e8	8f972230-ecc7-4413-b184-60025d7398b7	10/11/2023 9:25
fd114fd1-21e3-46a8-8c80-eb57d6af18e8	9df610bf-d62e-4110-aad8-5010f3699594	10/11/2023 9:27
fd114fd1-21e3-46a8-8c80-eb57d6af18e8	9df610bf-d62e-4110-aad8-5010f3699594	10/11/2023 9:25

Re: Return earliest non blank row by User ID

FreemanZ — Wed, 29 Nov 2023 02:06:48 GMT

Hi JamesMoody ,

could you also provide the expected result column directly?