Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago

Countrows with multiple conditions

Hi, I'd like to count rows with multiple conditions. The first measure seems to work well. But the second one doesn't work. The only thing I changed is the time difference, from 10s to 2hr. Event V2...
Need Help
amitchandak's avatar
amitchandak
Super User
5 years ago

Anonymous , Checked out the pbix. But not able to get the wrong data as it not the same(sample data) as the one you shared on the post.

 

Please share the expected output

 

Also try

countx(values(Sheet[SITE_NO]) ,calculate(COUNTROWS(Sheet1),
FILTER(
Sheet1,
[SITE_NO]=max([SITE_NO]) &&
[START_DT]>max([START_DT]) &&
[EVENT_CODE]=max([EVENT_CODE]) &&
DATEDIFF(min(EARLIER([END_DT])),max([START_DT]),SECOND)<10) , allselected(Sheet1)
))

Anonymous's avatar
Anonymous
Not applicable
5 years ago

Hi amitchandak 

Thanks for swift replies. I think I should clarify more. 

  • Events dax measure merges and counts the events that has same site number, same event code and the difference of less than or equal to 10s between current and previous event. 
  • Event V2 dax measure does the same whereas the difference is 2hr. 

Basically, I am trying to combine the repeated events, however they are recorded as separate rows. 

 

My expected output for Event V2 would be:

  • for Site No 4087 : 1 (refer to 1st pic, since its event start time and previous end time are <2hr)
  • for Site No 3841: 6 (refer to 2nd pic and the colour lines)