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
Anonymous's avatar
Anonymous
Not applicable
5 years ago

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. 

Anonymous's avatar
Anonymous
Not applicable
5 years ago

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's avatar
    Anonymous
    Not applicable
    5 years ago

    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.

     

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      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's avatar
        Anonymous
        Not applicable
        5 years ago

        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.