Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Erwin
Helper II
Helper II

Calculate sick report frequency

Hi all,

I need to show how many times an employee called in sick during a time period. I have a table of time registration data to do this. Below you will find a simplified example of this table.

image.png

Above table shows that employee 1201 called in sick on June 11 2018 for the first time (HourCode 160 = sick). On June 20 2018 he/she reported sick again. My aim is to come up with a calculation that shows that 1201 called in sick on two separate occasions.

 

My idea was to create a calculated column to single out the dates of the sick calls, June 11 and June 20. See below table.

image.png

I don't know if this is the best way to solve this problem and I also don't know how to create the DAX formula that would do above trick. Can you help me along?

 

Regards,

Erwin

7 REPLIES 7
Anonymous
Not applicable

Hi,

 

I believe I've written a calc that does what you're looking for:

 

Sick Occurence =
IF (
LOOKUPVALUE(
Sheet1[Hourcodekey],
Sheet1[Date],
CALCULATE (
MAX ( Sheet1[Date] ),
FILTER(
Sheet1,
Sheet1[Date] < EARLIER(Sheet1[Date]) &&
Sheet1[EmployeeKey] = EARLIER(Sheet1[EmployeeKey])
)
)
) <> 160
&& Sheet1[Hourcodekey] = 160
, 1
)
 
I tried to set this up to work across customers as well. Here's the result in the sample data:
Sick Frequency.PNG
Let me know if this works for you.
 
Ben
Anonymous
Not applicable

Thanks a lot! I get the following error when I try to apply this calculation in a similar dataset with multiple similar dates:
A table of multiple values was supplied where a single value was expected.

 

Dataset example:

 

DateStart timeHourcodekeyemployeekeySick occurence should be:
1-6-202009:001031201 
2-6-202009:001031201 
3-6-202009:0016012051
4-6-202009:001601205 
4-6-202009:001031201 
4-6-202015:001031201 
5-6-202009:001031201 
6-6-202009:0016012011
6-6-202009:0016012051
7-6-202009:001031201 
8-6-202009:001031201 
8-6-202009:0016012051
8-6-202009:001601205 
9-6-202009:001031201 
10-6-202009:001031201 
11-6-202009:001031201 
12-6-202009:0016012011
12-6-202015:001601201 
12-6-202009:001031205 
14-6-202009:001031201 
15-6-202009:001031201 

 

Hope someone can help us out

Hi bhpage,

 

Thanks for your response.

 

If I understand correctly the formula compares the hourcodekey of the current rowcontext with the hourcodekey of the preceeding rowcontext. If the hourcodekey in the current rowcontext is 160  and the hourcodekey in the preceeding rowcontext is not 160 than that is the day the employee called in sick for the first time.

 

Unfortunately the formula doesn't work with my real-life version of the data. The only thing different is that there are multiple employees in the real table who all have time registration data on the same dates. The error message reads "A table of multiple values was supplied where a single value was expected".

 

Can you solve this problem as well?

 

Rg. Erwin

 

 

Anonymous
Not applicable

Can you provide an example of a customer like this?

smpa01
Super User
Super User

@Erwincan you please provide a sample data to work with?

 

To be clear, you mentioned "that employee 1201 called in sick on June 11 2018 for the first time (HourCode 160 = sick). On June 20 2018 he/she reported sick again. " But 12,13,21 and 22 June 1201 has corresponding 160 too. What does that signify? Does it not mean he called in sick on those days too ?

 

If I understand it correctly, do you want to see for how many blocks of consecutive days someone called in sick ?

Did I answer your question? Mark my post as a solution!
Proud to be a Super User!
My custom visualization projects
Plotting Live Sound: Viz1
Beautiful News:Viz1, Viz2, Viz3
Visual Capitalist: Working Hrs

Hi smpa01,

 

You're right, I'm trying to count how many blocks of consecutive days 1201 called in sick (2 blocks). So I'm not trying to count then number of sick days themselves (6 days).

 

Rg. Erwin

Does this work for you?

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bdBNCkAhCEbRvTgO0q+f11tLtP9thEQQKkSDM9CLc1LPkmVQIuGiP1hoJfV6XN+BZqFb+CwMC3K3dX4QEZYI6xt70YWJ3/tbAQfzEeUhykOLStxF4E4C14a3bW0=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [DT = _t, HourCode = _t, EMP = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"DT", type date}, {"HourCode", Int64.Type}, {"EMP", Int64.Type}}),
    #"Filled Down" = Table.FillDown(#"Changed Type",{"HourCode", "EMP"}),
    #"Added Index2" = Table.AddIndexColumn(#"Filled Down", "Row#", 1, 1),
    #"Added Custom" = Table.AddColumn(#"Added Index2", "Custom", each if [HourCode]=160 then 1 else 0),
    #"Added Index" = Table.AddIndexColumn(#"Added Custom", "Index", 1, 1),
    #"Added Index1" = Table.AddIndexColumn(#"Added Index", "Index.1", 0, 1),
    #"Merged Queries" = Table.NestedJoin(#"Added Index1",{"Index.1"},#"Added Index1",{"Index"},"Added Index1",JoinKind.LeftOuter),
    #"Expanded Added Index1" = Table.ExpandTableColumn(#"Merged Queries", "Added Index1", {"Custom"}, {"Custom.1"}),
    #"Added Custom1" = Table.AddColumn(#"Expanded Added Index1", "Custom.2", each Text.From([Custom.1])&Text.From([Custom])),
    #"Filtered Rows" = Table.SelectRows(#"Added Custom1", each ([Custom.2] = "01")),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Custom.1", "Custom.2"}),
    #"Added Index3" = Table.AddIndexColumn(#"Removed Columns", "Index.2", 1, 1),
    #"Added Index4" = Table.AddIndexColumn(#"Added Index3", "Index.3", 0, 1),
    #"Merged Queries1" = Table.NestedJoin(#"Added Index4",{"Index.2"},#"Added Index4",{"Index.3"},"Added Index4",JoinKind.LeftOuter),
    #"Expanded Added Index4" = Table.ExpandTableColumn(#"Merged Queries1", "Added Index4", {"Index"}, {"Index.4"}),
    #"Added Custom2" = Table.AddColumn(#"Expanded Added Index4", "Custom.1", each if [Index.4]<> null then [Index.4]-1 else Table.RowCount(#"Filled Down")),
    #"Added Index5" = Table.AddIndexColumn(#"Added Custom2", "Index.5", 1, 1),
    #"Added Custom3" = Table.AddColumn(#"Added Index5", "Custom.2", each {[Index]+1..[Custom.1]}),
    #"Expanded Custom.2" = Table.ExpandListColumn(#"Added Custom3", "Custom.2"),
    #"Removed Other Columns" = Table.SelectColumns(#"Expanded Custom.2",{"Index.5", "Custom.2"}),
    Custom1 = let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bdBNCkAhCEbRvTgO0q+f11tLtP9thEQQKkSDM9CLc1LPkmVQIuGiP1hoJfV6XN+BZqFb+CwMC3K3dX4QEZYI6xt70YWJ3/tbAQfzEeUhykOLStxF4E4C14a3bW0=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [DT = _t, HourCode = _t, EMP = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"DT", type date}, {"HourCode", Int64.Type}, {"EMP", Int64.Type}}),
    #"Filled Down" = Table.FillDown(#"Changed Type",{"HourCode", "EMP"}),
    #"Added Index2" = Table.AddIndexColumn(#"Filled Down", "Row#", 1, 1),
    #"Added Custom" = Table.AddColumn(#"Added Index2", "Custom", each if [HourCode]=160 then 1 else 0),
    #"Added Index" = Table.AddIndexColumn(#"Added Custom", "Index", 1, 1),
    #"Added Index1" = Table.AddIndexColumn(#"Added Index", "Index.1", 0, 1),
    #"Merged Queries" = Table.NestedJoin(#"Added Index1",{"Index.1"},#"Added Index1",{"Index"},"Added Index1",JoinKind.LeftOuter),
    #"Expanded Added Index1" = Table.ExpandTableColumn(#"Merged Queries", "Added Index1", {"Custom"}, {"Custom.1"}),
    #"Added Custom1" = Table.AddColumn(#"Expanded Added Index1", "Custom.2", each Text.From([Custom.1])&Text.From([Custom])),
    #"Filtered Rows" = Table.SelectRows(#"Added Custom1", each ([Custom.2] = "01")),
    #"Added Custom2" = Table.AddColumn(#"Filtered Rows", "FRQ", each 1),
    #"Merged Queries1" = Table.NestedJoin(#"Added Custom1",{"Row#"},#"Added Custom2",{"Row#"},"Filtered Rows",JoinKind.LeftOuter),
    #"Expanded Filtered Rows" = Table.ExpandTableColumn(#"Merged Queries1", "Filtered Rows", {"FRQ"}, {"FRQ"})
in
    #"Expanded Filtered Rows",
    #"Merged Queries2" = Table.NestedJoin(#"Removed Other Columns",{"Custom.2"},Custom1,{"Row#"},"Table1",JoinKind.LeftOuter),
    #"Expanded Table1" = Table.ExpandTableColumn(#"Merged Queries2", "Table1", {"DT", "HourCode", "EMP", "Row#", "Custom", "Index", "Index.1", "Custom.1", "Custom.2", "FRQ"}, {"DT", "HourCode", "EMP", "Row#", "Custom", "Index", "Index.1", "Custom.1", "Custom.2.1", "FRQ"}),
    #"Filtered Rows1" = Table.SelectRows(#"Expanded Table1", each ([HourCode] = 160)),
    #"Replaced Value" = Table.ReplaceValue(#"Filtered Rows1",null,1,Replacer.ReplaceValue,{"FRQ"}),
    #"Merged Queries3" = Table.NestedJoin(Custom1,{"Index"},#"Replaced Value",{"Index"},"Custom1",JoinKind.LeftOuter),
    #"Expanded Custom1" = Table.ExpandTableColumn(#"Merged Queries3", "Custom1", {"FRQ"}, {"FRQ.1"}),
    #"Added Custom4" = Table.AddColumn(#"Expanded Custom1", "Custom.3", each if [FRQ]=null and [FRQ.1]=null then null else if [FRQ]<>null and [FRQ.1]=null then [FRQ] else [FRQ.1]),
    #"Renamed Columns" = Table.RenameColumns(#"Added Custom4",{{"FRQ", "FRQ_Start"}}),
    #"Removed Columns1" = Table.RemoveColumns(#"Renamed Columns",{"Custom", "Index", "Index.1", "Custom.1", "Custom.2", "FRQ.1"}),
    #"Sorted Rows" = Table.Sort(#"Removed Columns1",{{"Row#", Order.Ascending}}),
    #"Renamed Columns1" = Table.RenameColumns(#"Sorted Rows",{{"Custom.3", "FRQ_Consecution"}})
in
    #"Renamed Columns1"

Capture.JPG

Did I answer your question? Mark my post as a solution!
Proud to be a Super User!
My custom visualization projects
Plotting Live Sound: Viz1
Beautiful News:Viz1, Viz2, Viz3
Visual Capitalist: Working Hrs

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.