topic Re: Count rows with conditions in matrix in DAX Commands and Tips

Count rows with conditions in matrix

kentsharp — Sun, 05 Mar 2023 04:46:56 GMT

Hello,

Looking for some support on counting rows in a matrix if the conditions are met. The dataset i have is field crews working on multiple jobs in a day and i want to count if that day has sumofhours over certain thresholds. I have a measure if its <=12, but it's counthing the jobs on a day instead of it being the sumofhours on that day <=12 hours. How might it be best to do this?

Thanks

Kent

Re: Count rows with conditions in matrix

FreemanZ — Sun, 05 Mar 2023 05:55:37 GMT

hi kentsharp

focus on your expectation and orginal dataset.

Re: Count rows with conditions in matrix

kentsharp — Sun, 05 Mar 2023 06:01:24 GMT

Thanks for the response, but i'm not entirly sure I understand what your mean.

Re: Count rows with conditions in matrix

FreemanZ — Sun, 05 Mar 2023 06:04:55 GMT

hi kentsharp

i mean try to provide your sample dataset and expected result, all the others could be distracting.

Re: Count rows with conditions in matrix

tamerj1 — Sun, 05 Mar 2023 08:06:32 GMT

Hi kentsharp

you may try

SUMX (

VALUES ( 'Table'[Date] ),

VAR SumOfHours = [SumofHours]
RETURN

IF ( SumOfHours <= 12, SumOfHours )

)

Re: Count rows with conditions in matrix

kentsharp — Sun, 05 Mar 2023 14:43:39 GMT

I see...thanks

Below is the data. The resusult i'm looking for is to count the number of days an employee has specifc hours in a day. They can work on multiple jobs in a day.

time_entry_employee Date jobNumber Hours

Abdel Daoud	2023-02-01 0:00	22-0989-1	10
Alex Trenchie	2023-02-01 0:00	22-3115-0	6.5
Alex Trenchie	2023-02-01 0:00	22-6019-0	5.5
Barry Ubell	2023-02-01 0:00	18-1202-7	3.5
Barry Ubell	2023-02-01 0:00	23-0339-0	6
Benoit Hupe	2023-02-01 0:00	23-0069-0	2.5
Benoit Hupe	2023-02-01 0:00	23-0305-0	4.5
Benoit Hupe	2023-02-01 0:00	23-0306-0	2
Braden Parley	2023-02-01 0:00	22-3730-2	5.5
Braden Parley	2023-02-01 0:00	22-3738-0	4
Brady Sahulka	2023-02-01 0:00	22-0883-2	9
Camara Froese	2023-02-01 0:00	22-2268-1	5.5
Camara Froese	2023-02-01 0:00	23-0317-0	6.5
Caydan Lachapelle	2023-02-01 0:00	21-3430-0	2
Caydan Lachapelle	2023-02-01 0:00	22-2276-2	3.5
Caydan Lachapelle	2023-02-01 0:00	22-3863-0	5.5

Re: Count rows with conditions in matrix

FreemanZ — Sun, 05 Mar 2023 14:52:34 GMT

hi kentsharp

could you also provide your expected result in a table?

Re: Count rows with conditions in matrix

kentsharp — Sun, 05 Mar 2023 15:31:14 GMT

Re: Count rows with conditions in matrix

tamerj1 — Sun, 05 Mar 2023 15:37:49 GMT

kentsharp

Please try

=
SUMX (
SUMMARIZE ( 'Table', 'Table'[Name], 'Table'[Date] ),
VAR SumOfHours = [SumofHours] RETURN IF ( SumOfHours <= 12, SumOfHours )
)