Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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
Hi @kentsharp
you may try
SUMX (
VALUES ( 'Table'[Date] ),
VAR SumOfHours = [SumofHours]
RETURN
IF ( SumOfHours <= 12, SumOfHours )
)
Thanks for the response, but i'm not entirly sure I understand what your mean.
hi @kentsharp
i mean try to provide your sample dataset and expected result, all the others could be distracting.
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 |
Please try
=
SUMX (
SUMMARIZE ( 'Table', 'Table'[Name], 'Table'[Date] ),
VAR SumOfHours = [SumofHours] RETURN IF ( SumOfHours <= 12, SumOfHours )
)
User | Count |
---|---|
22 | |
11 | |
8 | |
6 | |
6 |
User | Count |
---|---|
26 | |
13 | |
11 | |
9 | |
6 |