topic Re: DAX equivalent to excel Array in DAX Commands and Tips

DAX equivalent to excel Array

cfstout — Wed, 12 Jan 2022 16:02:13 GMT

Hi,

I have an excel array formula that I need help in writing the DAX eqivalent.

Columns

Station ID

Start Time

End Time

My formula captures the start time of next start per Station ID.

Next Start Time - Excel Array Formula

{=MIN(IF(A3:A$4983=A2,IF(B3:B$4983>C2,B3:B$4983)))}

I would like to have this calculated in DAX, but cannot figure out equivalent.

Any help would be greatly appreciated.

/tks

Station ID	Start Time	End Time	Usage (kWh)	Peak kW	SiteID	ChgId	Duration	MicroSess	NextStartTime
100001-02	2021-09-01 10:37:35	2021-09-01 10:59:43	27	207	100001	2	0:22:08		13:46:23
100001-02	2021-09-01 13:46:23	2021-09-01 14:31:08	17	207	100001	2	0:44:45		17:35:09
100001-02	2021-09-01 17:35:09	2021-09-01 18:14:22	53	207	100001	2	0:39:13		15:47:56
100001-01	2021-09-02 12:04:22	2021-09-02 12:21:19	4	65	100001	1	0:16:57		14:52:53
100001-02	2021-09-02 15:47:56	2021-09-02 16:48:24	36	60	100001	2	1:00:28		11:11:17
100001-02	2021-09-03 11:11:17	2021-09-03 11:30:51	6	60	100001	2	0:19:34		16:09:11
100001-01	2021-09-03 14:52:53	2021-09-03 15:08:59	4	65	100001	1	0:16:06		18:16:58
100001-04	2021-09-04 14:18:24	2021-09-04 14:55:28	25	70.9	100001	4	0:37:04		12:01:55
100001-02	2021-09-04 16:09:11	2021-09-04 16:48:47	55	207	100001	2	0:39:36		16:59:52
100001-04	2021-09-05 12:01:55	2021-09-05 12:09:59	6	51.9	100001	4	0:08:04		16:35:09
100001-03	2021-09-05 16:10:09	2021-09-05 17:04:30	41	153.8	100001	3	0:54:21		11:30:26
100001-04	2021-09-05 16:35:09	2021-09-05 17:00:44	15	55.1	100001	4	0:25:35		14:14:16
100001-02	2021-09-05 16:59:52	2021-09-05 17:43:49	22	70.9	100001	2	0:43:57		15:18:53
100001-01	2021-09-05 18:16:58	2021-09-05 18:31:10	5	65	100001	1	0:14:12		20:06:39
100001-01	2021-09-05 20:06:39	2021-09-05 20:27:27	8	65	100001	1	0:20:48		0:12:45
100001-01	2021-09-06 00:12:45	2021-09-06 00:32:07	3	65	100001	1	0:19:22		17:07:49
100001-04	2021-09-06 14:14:16	2021-09-06 15:40:46	42	60	100001	4	1:26:30		20:54:52
100001-04	2021-09-07 20:54:52	2021-09-07 21:39:22	34	60	100001	4	0:44:30		0:00:00

Re: DAX equivalent to excel Array

ValtteriN — Wed, 12 Jan 2022 17:37:54 GMT

Hi,

This DAX should do what you want:

NextStart =

var curvalue = max(NextStart[Start Time])

var station = max(NextStart[Station ID])

return

CALCULATE(Min(NextStart[Start Time]),ALL(NextStart),NextStart[Start Time]>curvalue,NextStart[Station ID]=station)

End result:

The basic logic is to remove filters with ALL and keep the relevant filters with variables. You might need to add SiteID as an variable.

I hope this post helps to solve your issue and if it does consider accepting it as a solution and giving the post a thumbs up!

Re: DAX equivalent to excel Array

smpa01 — Wed, 12 Jan 2022 19:40:09 GMT

cfstout you can use a measure like this

NextStart = CALCULATE ( MIN ( 'tbl'[Start Time] ), 'tbl'[Start Time] > MAX ( 'tbl'[Start Time] ), ALLEXCEPT ( 'tbl', 'tbl'[Station ID] ) )

Re: DAX equivalent to excel Array

cfstout — Wed, 12 Jan 2022 22:15:54 GMT

Hi, thanks for the response.

I am unable to get this to work... would you mind attaching the .pbix file so I can see how you created the VAR?

tks/

Re: DAX equivalent to excel Array

cfstout — Wed, 12 Jan 2022 22:16:18 GMT

Hi, thanks for the response.

I am unable to get this to work... would you mind attaching the .pbix file so I can see how you created the VAR?

tks/

Re: DAX equivalent to excel Array

smpa01 — Wed, 12 Jan 2022 22:25:06 GMT

cfstout

Re: DAX equivalent to excel Array

cfstout — Thu, 13 Jan 2022 14:46:31 GMT

Thanks so much for your help. Works great!