Forum Discussion

_chris_'s avatar
_chris_
Helper III
2 years ago
Solved

Little measure problem

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

Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    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

4 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    2 years ago

    _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_'s avatar
      _chris_
      Helper III
      2 years ago

      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:

      DateStaffIdHoursProjectDay of Week
      07.10.202353762bla1Saturday
      07.10.202353765bla2Saturday
      13.10.202353765bla1Friday
      13.10.202353763bla2Friday
      14.10.202353762bla1Saturday
      14.10.202353761bla2Saturday
      15.10.202353763bla1Sunday
      15.10.202353764bla2Sunday

       

      That´s my date table:

      DateIsWeekend
      07.10.20231
      08.10.20231
      09.10.20230
      10.10.20230
      11.10.20230
      12.10.20230
      13.10.20230
      14.10.20231
      15.10.20231

       

      And that´s the expected output:

      DateStaffIdHoursWE>4WE<4
      07.10.20235376710
      13.10.20235376800
      14.10.20235376301
      15.10.20235376710

       

      It would be great if you could look into it, thanks!

       

      BTW: I voted for the feature request

       

      Thx, Christian

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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