topic Re: Count reset in DAX Commands and Tips

Count reset

matus_jun — Fri, 27 Dec 2024 08:38:31 GMT

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

Re: Count reset

DataNinja777 — Fri, 27 Dec 2024 13:15:41 GMT

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,

Re: Count reset

matus_jun — Mon, 30 Dec 2024 02:09:14 GMT

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

Re: Count reset

Anonymous — Mon, 30 Dec 2024 02:36:02 GMT

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.

Re: Count reset

matus_jun — Mon, 30 Dec 2024 02:46:11 GMT

wow It work thank you alot