Forum Discussion

matus_jun's avatar
matus_jun
Frequent Visitor
1 year ago
Solved

Count reset

Im sorry my english is not good 
what i want is count NewdayType if NewdayType is 1 continue counting if 0 set to 0 
base on emp_id

  • Anonymous's avatar
    Anonymous
    1 year ago

    Hi matus_jun ,

    Based on the testing, try using the following DAX formula to create a new column.

    CountByNewDayType = 
VAR CurrentEmployee = 'New day table'[employee_id]
VAR CurrentIndex = 'New day table'[Index]
VAR PreviousRows =
    FILTER(
        'New day table',
        'New day table'[employee_id] = CurrentEmployee &&
        'New day table'[Index] < CurrentIndex
    )
VAR PrevRowWithZero =
    MAXX(
        FILTER(
            PreviousRows,
            'New day table'[NewDayType] = 0
        ),
        'New day table'[Index]
    )
RETURN
IF(
    'New day table'[NewDayType] = 0,
    0,
    COUNTROWS(
        FILTER(
            PreviousRows,
            'New day table'[Index] > PrevRowWithZero
        )
    ) + 1
)

    Best Regards,

    Wisdom Wu

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

4 Replies

  • DataNinja777's avatar
    DataNinja777
    Super User
    1 year ago

    Hi matus_jun ,

     

    To calculate the running count of NewDayType based on your condition, where the count continues if NewDayType is 1 and resets to 0 if NewDayType is 0, while grouping by employee-id, you can create a calculated column in Power BI using DAX.

    The DAX formula for the calculated column is as follows:

    RunningCount = 
VAR CurrentIndex = 'Table'[Index]
VAR CurrentEmployee = 'Table'[employee-id]
VAR FilteredTable = 
    FILTER(
        'Table',
        'Table'[employee-id] = CurrentEmployee &&
        'Table'[Index] <= CurrentIndex
    )
RETURN
    IF(
        'Table'[NewDayType] = 0,
        0,
        COUNTROWS(
            FILTER(
                FilteredTable,
                'Table'[NewDayType] = 1
            )
        )
    )

    This formula works by first identifying the current row’s Index and employee-id to ensure the calculation is scoped correctly for each employee. It then filters the table to include only rows belonging to the current employee and with an Index less than or equal to the current row. If the NewDayType is 0 for the current row, the result is set to 0. Otherwise, it calculates the count of rows in the filtered table where NewDayType is 1.

    When applied, this will generate a column that resets to 0 whenever NewDayType is 0 and continues counting for NewDayType = 1 within the same employee-id. This ensures the running count logic respects the reset condition and the grouping by employee.

     

    Best regards,

    • matus_jun's avatar
      matus_jun
      Frequent Visitor
      1 year ago

      running count is not reset when newdaytype is 0  it should be 1,2,3,4,5,0,1,2,3,4,5,6 something like this
       Thank you alot for you help

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    Hi matus_jun ,

    Based on the testing, try using the following DAX formula to create a new column.

    CountByNewDayType = 
VAR CurrentEmployee = 'New day table'[employee_id]
VAR CurrentIndex = 'New day table'[Index]
VAR PreviousRows =
    FILTER(
        'New day table',
        'New day table'[employee_id] = CurrentEmployee &&
        'New day table'[Index] < CurrentIndex
    )
VAR PrevRowWithZero =
    MAXX(
        FILTER(
            PreviousRows,
            'New day table'[NewDayType] = 0
        ),
        'New day table'[Index]
    )
RETURN
IF(
    'New day table'[NewDayType] = 0,
    0,
    COUNTROWS(
        FILTER(
            PreviousRows,
            'New day table'[Index] > PrevRowWithZero
        )
    ) + 1
)

    Best Regards,

    Wisdom Wu

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.