Forum Discussion
IF Statement for a column not working
Hi, I am currently trying to create a column with an IF statement that returns back a 1 if true or 0 if false.
The table shows every time a user has taken a sick day in their period of working and each time is registered as a separate event, so for example, if someone called in sick for 2 days in a row, it still shows as two separate events. Also, this means the User ID comes up multiple times.
What the filter needs to do is to figure out whether a user has had 9 or more absences in a 4 month period, and if they have then for their ID mak down as 1 (true), if not then 0 (false).
There is a separate column for User ID, their start date, their end date, and a sick column then marks down as 1 or 0.5 per sickness leave.
It might be better to add up the sick days first in a column but I was not sure because it only matter if it is 9 or more absences in 4 months.
I've tried a lot of methods but I am not familiar with DAX yet and am unsure.
Would someone be able to help please?
Below is what I was working opn but it does not work:
if(([SicknessDays]>8), (datediff([StartDate], [EndDate], [60])), true(), false())
Hi Anonymous ,
Thanks for the data! I think I got to a solution:
I did it in two steps but both steps are already incorporated in the measure below. First, I created the column TomsRolling4Column which returns the 4 month window for each User ID (see picture above) This equals everything in the FILTER statement below (in green). Afterwards, we only need to distinct count all User IDs which have a number >= 9 in the new column (blue below).
TomsRolling4Measure = CALCULATE ( DISTINCTCOUNT ( Table[User ID] ), FILTER ( Table, CALCULATE ( SUM (Table[Sickness Amount (Days)] ), ALLEXCEPT ( Table, Table[User ID] ), DATESINPERIOD( Table[Sickness Date], Table[Sickness Date], -4, MONTH ) ) >= 9 ) )Please try it and let me know if it works 🙂
/Tom
https://www.tackytech.blog/
https://www.instagram.com/tackytechtom/
3 Replies
- tackytechtom
Most Valuable Professional
Hi Anonymous ,
Knowing that this is some pretty sensitive data you are working with, do you still think you could provide some anonymized / mockup sample data as well as the outcome you'd like to achieve?
Thanks!
/Tom
Hi tackytechtom ,
Thank you. Below I have aded a mockup of the data. The last column is the Count which returns a 1 (True) if someone has had 9 absences or more in a 4 month period. I am unsure if this is the best way to do it because all I need to do is have a Count on the Report that shows how many Users have had 9 or more absences in a 4 month period. I then need a way to reset that count live.
User 189 has had 12 absences but only 10 are in a 4 month period of each other so those have a Count of 1 but the other two do not. Also, the Sickness Amount has to add up to 9 or more bearing in mind that some have a count of 0.5 for half days.
Thank you,
Chezz
User ID Work Start Date Work End Date Sickness Date Sickness Amount (Days) Count 122 02/02/2015 05/10/2016 11/12/2015 1.0 0 122 02/02/2015 05/10/2016 12/12/2015 1.0 0 122 02/02/2015 05/10/2016 13/12/2015 1.0 0 122 02/02/2015 05/10/2016 03/09/2016 0.5 0 214 01/01/2018 04/06/2018 09/05/2018 1.0 0 214 01/01/2018 04/06/2018 02/06/2018 1.0 0 189 03/05/2019 14/12/2021 03/01/2020 1.0 1 189 03/05/2019 14/12/2021 04/01/2020 1.0 1 189 03/05/2019 14/12/2021 05/01/2020 1.0 1 189 03/05/2019 14/12/2021 06/01/2020 1.0 1 189 03/05/2019 14/12/2021 07/01/2020 1.0 1 189 03/05/2019 14/12/2021 17/02/2020 1.0 1 189 03/05/2019 14/12/2021 18/02/2020 1.0 1 189 03/05/2019 14/12/2021 19/02/2020 1.0 1 189 03/05/2019 14/12/2021 01/03/2020 1.0 1 189 03/05/2019 14/12/2021 02/03/2020 1.0 1 189 03/05/2019 14/12/2021 14/11/2021 1.0 0 189 03/05/2019 14/12/2021 15/11/2021 0.5 0
- tackytechtom
Hi Anonymous ,
Thanks for the data! I think I got to a solution:
I did it in two steps but both steps are already incorporated in the measure below. First, I created the column TomsRolling4Column which returns the 4 month window for each User ID (see picture above) This equals everything in the FILTER statement below (in green). Afterwards, we only need to distinct count all User IDs which have a number >= 9 in the new column (blue below).
TomsRolling4Measure = CALCULATE ( DISTINCTCOUNT ( Table[User ID] ), FILTER ( Table, CALCULATE ( SUM (Table[Sickness Amount (Days)] ), ALLEXCEPT ( Table, Table[User ID] ), DATESINPERIOD( Table[Sickness Date], Table[Sickness Date], -4, MONTH ) ) >= 9 ) )Please try it and let me know if it works 🙂
/Tom
https://www.tackytech.blog/
https://www.instagram.com/tackytechtom/