Little measure problem

[_chris_]

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

[v-yangliu-msft · Accepted 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

[_chris_]

Hi, thank you, this works perfectly!

[Greg_Deckler]

@_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.

[_chris_]

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