topic Re: Calculate count of occurances where a row value is immediately followed by other value in DAX Commands and Tips

Calculate count of occurances where a row value is immediately followed by other value

Rakesh_Bi — Fri, 16 Aug 2024 05:08:26 GMT

Hi Ashish_Mathur / All,

I need a help in writing the dax.

A table contains AccessId, DateTime, and Response code

AccessId ResponseCode DateTime

201 WeakMatch. 16/08/2024 11:11

201 NotFound 16/08/2024 11:13

201 NoInfo 16/08/2024 11:14

201 NotFound 16/08/2024 11:17

201 Match 16/08/2024 11:18

201 Match 16/08/2024 11:28

202 Match 16/08/2024 11:30

202 NotFound 16/08/2024 11:31

203 Match 16/08/2024 1133

203 NotFound 16/08/2024 11:35

204 NotFound 16/08/2024 1133

204 WeakMatch 16/08/2024 11:35

204 Match 16/08/2024 1133

204 NotFound 16/08/2024 11:35

204 Match 16/08/2024 11:37

Expected output

AccessId Count

201 1

204 1

Requirement is to count the number of occurances where NotFound is immediately following the Match based on the unique access id. In the above sample only 2 such conditions are satisfying. Below are the conditions matching.

201 NotFound 16/08/2024 11:17

201 Match 16/08/2024 11:18

204 NotFound 16/08/2024 11:35

204 Match 16/08/2024 11:37

Can you please help me in writing the dax as I am trying it from past week.

Thanks,

Rakesh

Re: Calculate count of occurances where a row value is immediately followed by other value

Ashish_Mathur — Fri, 16 Aug 2024 05:16:54 GMT

Hi,

PBI file attached.

Hope this helps.

Re: Calculate count of occurances where a row value is immediately followed by other value

Rakesh_Bi — Fri, 16 Aug 2024 07:21:55 GMT

Thanks for the quick response, I apologise, need to add another condition where it should calculate occurances within 3mins.

The dax provided is giving the expected output but whenever there are any other response codes in between NotFound and Match(ex: attached), it should ignore the count. And it should always check for the Match after immediate NotFound. In the below attachment for accessId it is counting for NotFound after Match which is incorrect.

Re: Calculate count of occurances where a row value is immediately followed by other value

Ashish_Mathur — Fri, 16 Aug 2024 07:28:54 GMT

You are welcome. Sharing a screenshot is useless. Share data in a format that can be pasted in an MS Excel file. Show the expected result very very clearly. Think through all your conditions very carefully and then post your question.

Re: Calculate count of occurances where a row value is immediately followed by other value

Rakesh_Bi — Fri, 16 Aug 2024 09:00:45 GMT

Apologise..!!

Attached Sample Data and Expected Output

Requirement: need to calculate occurances where NotFound and Match are appeared together within 3mins based on unique access Id and also it should sum the respective amount.

https://1drv.ms/x/s!AlZyY1FEcKn4cyNmDmiquIm4E0s

Re: Calculate count of occurances where a row value is immediately followed by other value

Ashish_Mathur — Fri, 16 Aug 2024 10:16:17 GMT

203 should not appear. PBI file attached.

Re: Calculate count of occurances where a row value is immediately followed by other value

Rashmi_ — Fri, 16 Aug 2024 14:34:17 GMT

Hi Ashish_Mathur,

I have a very similar case, in the above pbix file the responsecode from NotFound to Match to Match is also calculating but the expected output is to calculate NotFound to Match only

ResponseCode count Amount

201 1 130

204 1 200

Can you please help me on this

Re: Calculate count of occurances where a row value is immediately followed by other value

Ashish_Mathur — Sat, 17 Aug 2024 02:55:34 GMT

Your question is not clear. I have added some calculated columns in the attached file. See if that helps. DIY.

Re: Calculate count of occurances where a row value is immediately followed by other value

Rashmi_ — Tue, 20 Aug 2024 17:14:06 GMT

Hi Ashish_Mathur,

Thanks for sharing the pbix file, in the pbix file's date column is not in seconds, if the time difference is in seconds the outcome is not as expected

consider below case

ResponseCode previousresponsedate

NotFound 22/05/2024 11:33:25 Am

NotFound 22/05/2024 11:35:28 Am

Match 22/05/2024 11:35:35 Am

Match 22/05/2024 11:35:37 Am

Match 22/05/2024 11:35:38 Am

Expected output

AccessId occurances

206 1

It should consider 22/05/2024 11:35:28 Am - 22/05/2024 11:35:35 Am

My data source is JSON file and after importing I changed the date column to DateTime format. When there is difference in minutes it is counting occurances properly but for the same time and difference in seconds it is not showing proper results

I have used below calculations

Previous check date of not found = if([ResponseCode]="match", CALCULATE (max([checkDate]), FILTER(Sheet1, [AccessId]=EARLIER([AccessId]) && [ResponseCode]="notfound"&& [checkDate]<=EARLIER([checkDate]))), BLANK())

Response code of previous check date = CALCULATE (DISTINCTCOUNT(Sheet1 [ResponseCode]), FILTER(Sheet1, Sheet1 [AccessId]=EARLIER(Sheet1[AccessId]) &&Sheet1[checkDate] >EARLIER (Sheet1 [Previous check date of not found]) &&Sheet1 [checkDate]<EARLIER (Sheet1 [checkDate]))

Re: Calculate count of occurances where a row value is immediately followed by other value

Ashish_Mathur — Wed, 21 Aug 2024 02:04:27 GMT

May be someone else can help you with this.