Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago

Creating a Measure for Missing Scheduled Time

Good day,

 

I am VERY new to measures and could use some guidance please. I have a table consisting of scheduled events by employees in the future. (I have a date table that maps week numbers to each date, however in the sample data below I combined it all into one table). I'm trying to somehow add a measure to my schedule table that I can drag into the second picture below to add my 'missing time' for each week. By the missing time, I mean any employee's time that is < 8 hours for that day. So, for example, if the employee had two scheduled events for a day, each 3 hours, the measure would show 2 hours for that day.

 

Here's a simplified example of what I'm trying to evaluate (the yellow highlight):

 

Here's a screen shot of my report (only missing the data for the missing time in each week)

 

 

Here's the actual table 

 

 

THAAAAANK YOU!!!

Rob

 

Need Help DAX measure

5 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    4 years ago

    Anonymous , based on what I got. You can get remaining hour and use that with scheduled hour in stacked visual

     

    Remaining Hour =Sumx(Summmarize(Table, Table[Week], Table[Employee], "_1", Sum(Table[Scheduled Hour])), 8- [_1])

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      Amit, 

       

      First, THANK YOU! Just a couple of follow-up questions please:

      1. The _1 - is that literally just that, or is that shorthand for something? What function does that serve please?
      2. If the fields are in the same table (ie the second screen shot above you can see "Member_ID" (which is there first intial and last name concantenated) and "Hours_Sched" is in the same table. (With multiple events being scheduled in that table obviously). The Week # is just pulled from a Date Table that has a relationship from the Date_Time_Start to the Date (of the Dates table. All that table is is just a list of dates with the week and qtr #).
      3. Like I said, I'm new to DAX and so (ONLY if you have a spare moment) if you could kinda summarize the logic of what's happening you'd have NO idea how much that would help me

      Amit - thank you SO much for your quick and thoughtful response!

       

      -Rob

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Amit,

     

    I tried this:

     

    RemainingHours = Sumx(SUMMARIZE(ScheduleData, DateTable[Week], ScheduleData[Member_ID], "_1", SUM(ScheduleData[Hours_Sched])), 8-[_1])

     

    and it didn't appear to work:

     

    I'm sure it's something in my translation of your formula. I'm also curious what the "_1" parameter is doing?

     

    Thank you,

    Rob

  • v-luwang-msft's avatar
    v-luwang-msft
    Community Support
    4 years ago

    Hi  Anonymous ,

    Test the below measure:

     

    test1 =
IF (
    CALCULATE (
        SUM ( 'Table'[Hours Scheduled] ),
        FILTER (
            ALL ( 'Table' ),
            'Table'[Date] = MAX ( 'Table'[Date] )
                && 'Table'[Employee] = MAX ( 'Table'[Employee] )
        )
    ) <= 8,
    8
        - CALCULATE (
            SUM ( 'Table'[Hours Scheduled] ),
            FILTER (
                ALL ( 'Table' ),
                'Table'[Date] = MAX ( 'Table'[Date] )
                    && 'Table'[Employee] = MAX ( 'Table'[Employee] )
            )
        ),
    0
)

     

     

    Best Regards

    Lucien

  • v-luwang-msft's avatar
    v-luwang-msft
    Community Support
    4 years ago

    Hi Anonymous ,

    Has your problem been solved, if so, please consider Accept a correct reply as the solution or share your own solution to help others find it.

    Best Regards
    Lucien