Forum Discussion
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...
Hi Anonymous
By site 4780 and 3841, I was referring to screenshots in my message 1. Anyway, as for sample data, the expected output (site B, event OB) should be 27. I think you created the right measure.
Hi Anonymous ,
The measures are:
Measure = VAR y = ADDCOLUMNS(
Sheet1,"Batch",
CALCULATE(
COUNTROWS(Sheet1),
FILTER(
Sheet1,
[SITE_NO]=EARLIER([SITE_NO]) &&
[START_DT]>EARLIER([START_DT]) &&
[EVENT_CODE]=EARLIER([EVENT_CODE]) &&
DATEDIFF(EARLIER([END_DT]),[START_DT],SECOND)<=7200)
))
RETURN SUMX(
y,[Batch])
Measure 2 = VAR y = ADDCOLUMNS(
Sheet1,"Batch",
CALCULATE(
COUNTROWS(Sheet1),
FILTER(
Sheet1,
[SITE_NO]=EARLIER([SITE_NO]) &&
[START_DT]>EARLIER([START_DT]) &&
[EVENT_CODE]=EARLIER([EVENT_CODE]) &&
DATEDIFF(EARLIER([END_DT]),[START_DT],SECOND)<=10)
))
RETURN SUMX(
y,[Batch])
Please try.
Aiolos Zhao
Hi Anonymous
Thanks for quick reply. Your measures are similar to mine in first post.
However, in large dataset, Measure 2 works but somehow Measure doesn't work.
Hi Anonymous ,
I think if the columns are not changed in your large data, the measure will also work.
You may need to show the data model, columns in the tables, and selections in the dashboard, and the case which doesn't work.
That may help others to figure out the problem.
Aiolos Zhao