[DAX] Calculate with filter?

Question

I 've got the following issue:&nbsp;&nbsp;My employees got their working hours per week. It's a table in AX 365, see image:I sum the hours in my report so I got the working hours per week (see A: totaal werkuren) . Employee A works 40 hours a week and employee B works 36 hours a week. I want to calculate the&nbsp;availabilty where 40 hours a week = 100%. So I divide the sum of working hours (40 or 36) by 40 and multiply it with 100 (so: 100% or 90%)&nbsp;(see B: inzetbaarheid).&nbsp;IsWorkDay (C) is a datetable with the following DAX:&nbsp;IsWorkDay = SWITCH(WEEKDAY([Date]);1;0;7;0;1). This works fine.Werkuren per maand (D) is calculated:&nbsp;CALCULATE(SUM('Calendar'[IsWorkDay])) * [Werkuren 8]. Werkuren 8 is a measure with value 8. I also have a measure Werkuren 9 with the value 9. This goes well for someone who works 5 days a week, 8 hours a day, like employee A. Employee B works 4 days a week, 9 hours a day. Of course, for him column C (isWorkDay) will be less then employee A. I'm afraid I need to create a new measure for him, right? In that case, column D will be easy to calculate: new measure IsWorkDay * measure Werkuren 9. But it would be nice to do it all in one measure / column etc because otherwise it's not really easy to put all my data in 1 matrix. That's why I'm thinking about using Calculate combined with Filter but I'm nog sure it's the right way to solve this.&nbsp;Maybe you guys have some advice?&nbsp;

v-yulgu-msft · Answer

Hi&nbsp;RemiAnthonise,
&nbsp;
Maybe you could try:
Werkuren per maand =
IF (
    SELECTEDVALUE ( tablename[Employee] ) = "EmployeeA",
    CALCULATE ( SUM ( 'Calendar'[IsWorkDay] ) ) * [Werkuren 8],
    CALCULATE ( SUM ( 'Calendar'[IsWorkDay] ) ) * [Werkuren 9]
)

Regards,
Yuliana Gu

remianthonise · Answer

Hi&nbsp;v-yulgu-msft&nbsp;,&nbsp;Thank you for your reply. First of all, I think we miss a thing. I have a calculated column which gives me the workdays in a month, that's&nbsp;IsWorkDay 5 days = SWITCH(WEEKDAY([date]); 1;0;7;0;1). In my case, employee B only works 4 days. So I have to add an extra column which gives me the workdays a month when the workweeks lasts 4 days. But I can't find the right way to modify the DAX this way:&nbsp;IsWorkDay 4 days = SWITCH(WEEKDAY([date]);&nbsp;????For example, I've tried&nbsp;IsWorkDay 4&nbsp;days = SWITCH(WEEKDAY([date]); 1;0;7;-1;1) but this doesn't work well for every month. For example: september 2018 gives me 15 days where it should be 16.&nbsp;After having this calculated column I think we can go further with your formula.Werkuren per maand =
IF (
    SELECTEDVALUE ( tablename[Employee] ) = "EmployeeA", IsWorkDay 4&nbsp;days * [Werkuren 9];
    IsWorkDay 5&nbsp;days * [Werkuren 8]

)&nbsp;Than it would be something like this.

remianthonise · Answer

I did the following:&nbsp;I've created a new column&nbsp;Weekend / weekdays = switch(true();'Calendar'[Weekday] = "vrijdag"; "0";'Calendar'[Weekday] = "zaterdag"; "0";'Calendar'[Weekday] = "zondag"; "0";'Calendar'[Weekday] = "maandag"; "1";'Calendar'[Weekday] = "dinsdag"; "1";'Calendar'[Weekday] = "woensdag"; "1";'Calendar'[Weekday] = "donderdag"; "1";blank())where 0 is weekend and 1 is workday. After that, I used the formula that was mentioned before. As you can see in the below image, the total is correct. The lines in my table give me wrong data. How can I solve this?&nbsp;