Count Across Multiple Columns

[Anonymous]

Hello, I have a table which look like below, which show the number of work hours per week.

Week     employeeid Sunday Monday Tuesday Wednesday Thursday Friday Saturday

12                  1                8          8.5          12.5         13                14        9           0

How can we calculate in DAX the number of time the employee is working more than 12 hours.  For EmployeeID=1 the answser will be 3.

Thanks in advance for your assistance.

@Anonymous

[Anonymous]

Try unpivoting your data in the PowerQuery editor (select the first two columns and Unpivot Other Columns.  Now you will have a table with rows, split by employee id and week.  This will be a much better data structure to work with for your report.  Rename the columns after pivoting to Hours and Day Name

The measure you would need would look like : Days Overworked = CALCULATE(COUNTROWS('Table'), Hours > 😎

You could add a calculated column to get a combination of week and day, or perhaps day number.  

You will also be able to split the hours worked by weekday and aggregate to month / fiscal period, if you have a lookup table to join to your week/ day column. 

[Anonymous]

Hello,

Thanks for your help.  I have added a new column using the following script and it works.

Number of Time Overruns =

var Criteria=12

return

    IF ( 'Exceeding Normal Work Hours per Day'[Sunday]> criteria , 1, 0 )

        + IF ( [Monday]> criteria , 1, 0 )

        + IF ( [Tuesday]> criteria , 1, 0)

        + IF ( [Wednesday]> criteria , 1, 0 )

        + IF ( [Thursday]> criteria , 1, 0 )

        + iF ( [Friday] > criteria,1,0)

        + if ( [Saturday] > criteria,1,0)

[Anonymous · Accepted solution]

Hi, that works too - try unpivoting your data, it will give you other benefits too in terms of charting and displaying your data, without losing information