Count the Number of Sick Occasions in Dax - Calculated Column - PowerBI

[MikeCobra]

Hi All,

I was wondering if anyone know how to count the number of Sick Occasions in Dax? i know how in Excel but dax is a completely different beast.

Looking at the example i have posted. I want it to show 0 if they were not absent. Then increment once they become absent.

But then they return to work and then become ill again. I want it to show 2 this time. As seen in the example column (H). Also for it to be related to each persons Employee ID. so that it can reset etc.

Just so i can track the number of occasions a colleague has gone off sick. I have tried Power Query but it never finished loading due to the size of the data (over 30000 rows)

I also tried in dax using the Earlier() function but could never get it to work.

Any help would be greatly appreciated.

Thanks MikeCobra

 

[JEC]

Here a solution in Power Query. I formatted the first groupby step for better readability.

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    grp = Table.Group(Source, {"Employee name", "Employee ID"}, 
        {
            {"tbl", each  
                Table.AddColumn(
                    Table.AddIndexColumn(_,"ind",0),"tCol", each let r = Source[Absent]{[ind]-1} in 
                            try if r =0 and [Absent] = 1 then [ind] 
                                else if  r =1 and [Absent] = 0 then 0 else null 
                            otherwise 0
                        ) 

            }
        }
    ),
    exp = Table.Combine(grp[tbl]),
    fill = Table.FillDown(exp,{"tCol"}),
    grp2 = Table.Group(fill, {"Employee name", "tCol"}, {{"Count", each _}}),
    grp3 = Table.Group(grp2, {"Employee name"}, {{"tbl", each Table.AddIndexColumn(_,"Sick Occasions",0)}}),
    index = Table.Combine(grp3[tbl]),
    exp2 = Table.ExpandTableColumn(index, "Count", {"Date", "Present", "Authorised Holiday", "Absent"}, {"Date", "Present", "Authorised Holiday", "Absent"}),
    dCol = Table.RemoveColumns(exp2,{"tCol"}),
    cType = Table.TransformColumnTypes(dCol,{{"Date", type date}}),
    result = Table.Sort(cType,{{"Employee name", Order.Ascending}, {"Date", Order.Ascending}})
in
    result

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	Employee name	Employee ID	Date	Present	Authorised Holiday	Absent		Employee name	Date	Present	Authorised Holiday	Absent	Sick Occasions
2	a	11	5-2-2024	1	0	0		a	5-2-2024	1	0	0	0
3	a	11	6-2-2024	1	0	0		a	6-2-2024	1	0	0	0
4	a	11	7-2-2024	1	0	0		a	7-2-2024	1	0	0	0
5	a	11	8-2-2024	0	0	1		a	8-2-2024	0	0	1	1
6	a	11	9-2-2024	0	0	1		a	9-2-2024	0	0	1	1
7	a	11	12-2-2024	0	0	1		a	12-2-2024	0	0	1	1
8	a	11	13-2-2024	1	0	0		a	13-2-2024	1	0	0	0
9	a	11	14-2-2024	0	0	1		a	14-2-2024	0	0	1	2
10	a	11	15-2-2024	0	0	1		a	15-2-2024	0	0	1	2
11	a	11	16-2-2024	0	1	0		a	16-2-2024	0	1	0	0
12	b	12	5-2-2024	1	0	0		b	5-2-2024	1	0	0	0
13	b	12	6-2-2024	1	0	0		b	6-2-2024	1	0	0	0
14	b	12	7-2-2024	1	0	0		b	7-2-2024	1	0	0	0
15	b	12	8-2-2024	0	0	1		b	8-2-2024	0	0	1	1
16	b	12	9-2-2024	0	0	1		b	9-2-2024	0	0	1	1
17	b	12	12-2-2024	0	0	1		b	12-2-2024	0	0	1	1
18	b	12	13-2-2024	1	0	0		b	13-2-2024	1	0	0	0
19	b	12	14-2-2024	0	0	1		b	14-2-2024	0	0	1	2
20	b	12	15-2-2024	0	0	1		b	15-2-2024	0	0	1	2
21	b	12	16-2-2024	0	1	0		b	16-2-2024	0	1	0	0
Sheet1

 

[MikeCobra]

hi JEC,

I have tried this but because of my dataset (over 30000 rows)

It just constantly loads and never finishes the loading

Thanks

 

[JEC]

Hmm you could try to buffer steps. Give this a try

let
    Source = Table.Buffer(Excel.CurrentWorkbook(){[Name="Table1"]}[Content]),
    grp = Table.Group(Source, {"Employee name", "Employee ID"}, 
        {
            {"tbl", each  
                Table.AddColumn(
                    Table.AddIndexColumn(_,"ind",0),"tCol", each let r = Source[Absent]{[ind]-1} in 
                            try if r =0 and [Absent] = 1 then [ind] 
                                else if  r =1 and [Absent] = 0 then 0 else null 
                            otherwise 0
                        ) 

            }
        }
    ),
    exp = Table.Combine(grp[tbl]),
    fill = Table.Buffer(Table.FillDown(exp,{"tCol"})),
    grp2 = Table.Group(fill, {"Employee name", "tCol"}, {{"Count", each _}}),
    grp3 = Table.Group(grp2, {"Employee name"}, {{"tbl", each Table.AddIndexColumn(_,"Sick Occasions",0)}}),
    index = Table.Combine(grp3[tbl]),
    exp2 = Table.ExpandTableColumn(index, "Count", {"Date", "Present", "Authorised Holiday", "Absent"}, {"Date", "Present", "Authorised Holiday", "Absent"}),
    dCol = Table.RemoveColumns(exp2,{"tCol"}),
    cType = Table.TransformColumnTypes(dCol,{{"Date", type date}}),
    result = Table.Sort(cType,{{"Employee name", Order.Ascending}, {"Date", Order.Ascending}})
in
    result

 

[MikeCobra]

Hi JEC,

The buffer has allowed the code to run but its not running correctly

Like for this instance - it has found that it is a sickness but because they come after each other. They should be the same index rather than a new one. Since it is still apart of the same instance

See attached. They should both be 1 rather than 1 and 2.

Thanks Mike