Forum Discussion

cfstout's avatar
cfstout
Regular Visitor
4 years ago
Solved

DAX equivalent to excel Array

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 IDStart TimeEnd TimeUsage (kWh)Peak kWSiteIDChgIdDurationMicroSessNextStartTime
100001-022021-09-01 10:37:352021-09-01 10:59:432720710000120:22:08 13:46:23
100001-022021-09-01 13:46:232021-09-01 14:31:081720710000120:44:45 17:35:09
100001-022021-09-01 17:35:092021-09-01 18:14:225320710000120:39:13 15:47:56
100001-012021-09-02 12:04:222021-09-02 12:21:1946510000110:16:57 14:52:53
100001-022021-09-02 15:47:562021-09-02 16:48:24366010000121:00:28 11:11:17
100001-022021-09-03 11:11:172021-09-03 11:30:5166010000120:19:34 16:09:11
100001-012021-09-03 14:52:532021-09-03 15:08:5946510000110:16:06 18:16:58
100001-042021-09-04 14:18:242021-09-04 14:55:282570.910000140:37:04 12:01:55
100001-022021-09-04 16:09:112021-09-04 16:48:475520710000120:39:36 16:59:52
100001-042021-09-05 12:01:552021-09-05 12:09:59651.910000140:08:04 16:35:09
100001-032021-09-05 16:10:092021-09-05 17:04:3041153.810000130:54:21 11:30:26
100001-042021-09-05 16:35:092021-09-05 17:00:441555.110000140:25:35 14:14:16
100001-022021-09-05 16:59:522021-09-05 17:43:492270.910000120:43:57 15:18:53
100001-012021-09-05 18:16:582021-09-05 18:31:1056510000110:14:12 20:06:39
100001-012021-09-05 20:06:392021-09-05 20:27:2786510000110:20:48 0:12:45
100001-012021-09-06 00:12:452021-09-06 00:32:0736510000110:19:22 17:07:49
100001-042021-09-06 14:14:162021-09-06 15:40:46426010000141:26:30 20:54:52
100001-042021-09-07 20:54:522021-09-07 21:39:22346010000140:44:30 0:00:00
array
DAX

6 Replies

  • smpa01's avatar
    smpa01
    Community Champion
    4 years ago

    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] )
)

     

     

     

  • ValtteriN's avatar
    ValtteriN
    Community Champion
    4 years ago

    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!


    • cfstout's avatar
      cfstout
      Regular Visitor
      4 years ago

      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/