Forum Discussion

bikgx's avatar
bikgx
Frequent Visitor
3 years ago
Solved

Count Row with multi condition and multi cut off time

Hi all;   i am a beginner but tried to figured out my issue by my self and search on this community but still confused,  i have dataset ( attached here),   https://docs.google.com/spreadsheets/d/1Q...
  • Anonymous's avatar
    Anonymous
    3 years ago

    bikgx , please try the following measure:

     

    WaitingJobCount = 
    SUMX(
        'Table1',
        VAR vTime = 
            SWITCH('Table1'[Client]
                ,"AAA", TIME(16, 30, 0)
                ,"BBB", TIME(13, 30, 0)
                ,TIME(15, 0, 0)
            )
        VAR vStartTime = (TODAY()-1) + vTime
        VAR vEndTime = TODAY() + vTime
        VAR vJobWaitingCount =
        IF(
          'Table1'[picking_state] = "waiting"
          && 'Table1'[DateCreated] > vStartTime && 'Table1'[DateCreated] <= vEndTime
          , 1
          , 0
        )
        RETURN vJobWaitingCount
    )

     

    This give me these results below:

     

     

Anonymous's avatar
Anonymous
Not applicable
3 years ago

bikgx , please try the following measure:

 

WaitingJobCount = 
    SUMX(
        'Table1',
        VAR vTime = 
            SWITCH('Table1'[Client]
                ,"AAA", TIME(16, 30, 0)
                ,"BBB", TIME(13, 30, 0)
                ,TIME(15, 0, 0)
            )
        VAR vStartTime = (TODAY()-1) + vTime
        VAR vEndTime = TODAY() + vTime
        VAR vJobWaitingCount =
        IF(
          'Table1'[picking_state] = "waiting"
          && 'Table1'[DateCreated] > vStartTime && 'Table1'[DateCreated] <= vEndTime
          , 1
          , 0
        )
        RETURN vJobWaitingCount
    )

 

This give me these results below: