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

[Rakesh_Bi]

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

[Rashmi_]

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

[Ashish_Mathur]

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

[Rashmi_]

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

[Ashish_Mathur]

May be someone else can help you with this.

[Ashish_Mathur]

Hi,

PBI file attached.

Hope this helps.

[Rakesh_Bi]

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. 

 

 

 

[Ashish_Mathur]

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.

[Rakesh_Bi]

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 

[Ashish_Mathur · Accepted solution]

203 should not appear.  PBI file attached.