Countrows with multiple conditions

[Anonymous]

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.

[AiolosZhao]

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

[Anonymous]

Hi @AiolosZhao 

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. 

[AiolosZhao]

Hi @Anonymous ,

What's your the expected result of your sample data, in your sample data, the Site No is A / B / C, NO 4780 and 3841

And I use the measure for SITE NO = B and EVENT_CODE = OB, the result is 24 and 27.

Aiolos Zhao

[Anonymous]

Hi @AiolosZhao 

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.

[AiolosZhao]

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

[Anonymous]

Hi @AiolosZhao 

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. 

[AiolosZhao]

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

[amitchandak]

@Anonymous , Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data. Along with Formula logic

[Anonymous]

Hi,

Please download the sample pbix from gdrive. 

https://drive.google.com/file/d/1g0__wX8wCgRGSLMwzDWuzR8a5zWki09n/view?usp=sharing 

[amitchandak]

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

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)