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 a...
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

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