Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi,
I have a fact table with time bookings of employees and a date table which are related through the date field.
I need two measures to figure out the number of occurences of weekend work according to the following rules:
Every saturday or sunday (Date[IsWeekend]) an employee worked between 0 and 4 hrs, the measure WE<4 should be 1 and in case of having worked more than 4 hrs the meaure WE>4 should be 1.
With my skills, I would be able to summarize alle the hours if weekend days, where an employee worked between 0 and 4 hrs. But whenI grouping by Quarter instead of the number of occurences, I am getting the total number of hours.
That´s where I am stuck. So any help would be great.
Thanks, Christian
Solved! Go to Solution.
Hi @_chris_ ,
Here are the steps you can follow:
1. Create measure.
WE<4 =
var _sum=
IF(
MAX('Table'[Day of Week]) in {"Saturday","Sunday"},
SUMX(
FILTER(ALL('Table'),
'Table'[Date]=MAX('Table'[Date])),[Hours]),BLANK())
return
IF(
_sum >=0 && _sum <=4&&_sum <>BLANK(),1,0)
WE>4 =
var _sum=
IF(
MAX('Table'[Day of Week]) in {"Saturday","Sunday"},
SUMX(
FILTER(ALL('Table'),
'Table'[Date]=MAX('Table'[Date])),[Hours]),BLANK())
return
IF(
_sum >4&&_sum <>BLANK(),1,0)
2. Result:
Best Regards,
Liu Yang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly
Hi @_chris_ ,
Here are the steps you can follow:
1. Create measure.
WE<4 =
var _sum=
IF(
MAX('Table'[Day of Week]) in {"Saturday","Sunday"},
SUMX(
FILTER(ALL('Table'),
'Table'[Date]=MAX('Table'[Date])),[Hours]),BLANK())
return
IF(
_sum >=0 && _sum <=4&&_sum <>BLANK(),1,0)
WE>4 =
var _sum=
IF(
MAX('Table'[Day of Week]) in {"Saturday","Sunday"},
SUMX(
FILTER(ALL('Table'),
'Table'[Date]=MAX('Table'[Date])),[Hours]),BLANK())
return
IF(
_sum >4&&_sum <>BLANK(),1,0)
2. Result:
Best Regards,
Liu Yang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly
Hi, thank you, this works perfectly!
@_chris_ Sounds like in your measure you need to SUMMARIZE by occurence and then SUMX across it, basically a measure's total problem. Can you post sample data as text? Otherwise, this may help:
First, please vote for this idea: https://ideas.powerbi.com/ideas/idea/?ideaid=082203f1-594f-4ba7-ac87-bb91096c742e
This looks like a measure totals problem. Very common. See my post about it here: https://community.powerbi.com/t5/DAX-Commands-and-Tips/Dealing-with-Measure-Totals/td-p/63376
Also, this Quick Measure, Measure Totals, The Final Word should get you what you need:
https://community.powerbi.com/t5/Quick-Measures-Gallery/Measure-Totals-The-Final-Word/m-p/547907
Also: https://youtu.be/uXRriTN0cfY
And: https://youtu.be/n4TYhF2ARe8
You may also get some mileage from this. This looks like a measure aggregation problem. See my blog article about that here: https://community.powerbi.com/t5/Community-Blog/Design-Pattern-Groups-and-Super-Groups/ba-p/138149
The pattern is:
MinScoreMeasure = MINX ( SUMMARIZE ( Table, Table[Group] , "Measure",[YourMeasure] ), [Measure])
MaxScoreMeasure = MAXX ( SUMMARIZE ( Table, Table[Group] , "Measure",[YourMeasure] ), [Measure])
AvgScoreMeasure = AVERAGEX ( SUMMARIZE ( Table, Table[Group] , "Measure",[YourMeasure] ), [Measure])
etc.
Hi, @Greg_Deckler,
first of all thank you very much for your reply. I tried to follow your links but I did not get it done. When calculating the totals, I do not need to refer to the number of hours but to the number of occurences. This makes it hard for me to understand.
So I decided to post the data as text and ask you to help me again:
Fact table:
Date | StaffId | Hours | Project | Day of Week |
07.10.2023 | 5376 | 2 | bla1 | Saturday |
07.10.2023 | 5376 | 5 | bla2 | Saturday |
13.10.2023 | 5376 | 5 | bla1 | Friday |
13.10.2023 | 5376 | 3 | bla2 | Friday |
14.10.2023 | 5376 | 2 | bla1 | Saturday |
14.10.2023 | 5376 | 1 | bla2 | Saturday |
15.10.2023 | 5376 | 3 | bla1 | Sunday |
15.10.2023 | 5376 | 4 | bla2 | Sunday |
That´s my date table:
Date | IsWeekend |
07.10.2023 | 1 |
08.10.2023 | 1 |
09.10.2023 | 0 |
10.10.2023 | 0 |
11.10.2023 | 0 |
12.10.2023 | 0 |
13.10.2023 | 0 |
14.10.2023 | 1 |
15.10.2023 | 1 |
And that´s the expected output:
Date | StaffId | Hours | WE>4 | WE<4 |
07.10.2023 | 5376 | 7 | 1 | 0 |
13.10.2023 | 5376 | 8 | 0 | 0 |
14.10.2023 | 5376 | 3 | 0 | 1 |
15.10.2023 | 5376 | 7 | 1 | 0 |
It would be great if you could look into it, thanks!
BTW: I voted for the feature request
Thx, Christian
User | Count |
---|---|
92 | |
85 | |
77 | |
66 | |
62 |
User | Count |
---|---|
110 | |
95 | |
95 | |
64 | |
59 |