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 count should return as 1.
Events = VAR y = ADDCOLUMNS(
TABLE,"Batch",
1-CALCULATE(
COUNTROWS(TABLE),
FILTER(
TABLE,
TABLE[SITE_NO]=EARLIER(TABLE[SITE_NO]) &&
TABLE[START_DT]>EARLIER(TABLE[START_DT]) &&
TABLE[EVENT_CODE]=EARLIER(TABLE[EVENT_CODE]) &&
DATEDIFF(EARLIER(TABLE[END_DT]),TABLE[START_DT],SECOND)<10
)))
RETURN SUMX(
y,[Batch])Events V2 = VAR y = ADDCOLUMNS(
TABLE,"Batch",
1-CALCULATE(
COUNTROWS(TABLE),
FILTER(
TABLE,
TABLE[SITE_NO]=EARLIER(TABLE[SITE_NO]) &&
TABLE[START_DT]>EARLIER(TABLE[START_DT]) &&
TABLE[EVENT_CODE]=EARLIER(TABLE[EVENT_CODE]) &&
DATEDIFF(EARLIER(TABLE[END_DT]),TABLE[START_DT],SECOND)<7201
)))
RETURN SUMX(
y,[Batch])
Please find another example below. Event V2 should return as 6.
11 Replies
Anonymous , Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data. Along with Formula logic
Hi,
Please download the sample pbix from gdrive.
https://drive.google.com/file/d/1g0__wX8wCgRGSLMwzDWuzR8a5zWki09n/view?usp=sharing
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)
))
Hi Anonymous ,
I think you need to show the expected result of your sample data.
I use your sample data to create a calculated column, hope that's what you want, please try.
Column = SUMX(Sheet1,IF([SITE_NO]=EARLIER([SITE_NO]) && [START_DT]>EARLIER([START_DT]) && [EVENT_CODE]=EARLIER([EVENT_CODE]) && DATEDIFF(EARLIER(Sheet1[END_DT]),Sheet1[START_DT],SECOND) <= 10, 1, 0))Aiolos Zhao
Hi Anonymous
Please read message 5 for expected output. The sample data size is so tiny compared to the one I am working on.
I am afraid I won't be able to add the calculated column for years worth of data (approx. 10M rows). That's why I wanted DAX measure to combine repeated events with above conditions.
