How to calculate strength of students in a Class using DAX?

[Anonymous]

Hello All,

I have a requirement as i need to calculate the present strength of students in class.

Example.:-

Class A having 5 students A, B, C, D, E.

Now i will be getting the infomration from id swipecard machines that whether they are entered into the class or exited.

So now my data looks like this.

Student ID	Time	In/Out
A	8:10:00 AM	In
B	8:20:00 AM	In
C	8:34:00 AM	In
D	8:35:00 AM	In
A	8:43:00 AM	Out
A	8:56:00 AM	In
E	9:10:00 AM	In
C	9:15:00 AM	Out
C	9:23:00 AM	In
D	9:47:00 AM	Out
B	9:59:00 AM	Out
B	10:00:00 AM	In
D	10:10:00 AM	In
A	11:12:00 AM	Out
B	11:34:00 AM	Out
C	12:01:00 PM	Out
D	12:31:00 PM	Out
E	1:02:00 PM	Out

Now If i categorige these timings with every half an hour duration, and calculate the strength of students that are present in class.

the expected output is as looks like this.

Duration	Strength In Class
8:00 to 8:30	2 (A,B IN)
8:30 to 9:00	4 (B is IN already, C,D are New Entries, and A exited and enterd again)
9:00 to 9:30	5 (A,B,D Are IN already,E New Entry, and C exites and entered again)
9:30 to 10:00	3 (A, C, E are still in IN, but B and D are exited)
10:00 to 10:30	5 (All are present IN class)
10:30 to 11:00	5 (All are present IN class)
11:00 to 11:30	4 (A exited, B,C,D,E still IN Class)
11:30 to 12:00	3 (B Exited, C,D E still IN Class)
12:00 to 12:30	2 (C Exited, D,E Still IN Class)
12:30 to 1:00 PM	1 (D Exited, E Still IN Class)
1:00 to 1:30 PM	0 ()

So how can i calculate this in powerbi.

Please help.

I will be so thankful for your ideas sugestions.

let me know if you need any extra information.

Thanks,

Mohan V

[v-juanli-msft]

Hi @Anonymous

There are some calculated columns created in your example dataset.

Please have a look and try based on my pbix.

Since there is no value between "10:30 to 11:00", my formula can't convert this period for the table.

If your data is not very large and complex, a temporary way is to enter data in a new table of this row and union it to your table.

If you have trouble doing this, please let me know.

Best Regards

Maggie

[Anonymous]

Thanks for the reply @v-juanli-msft.

That something unexpected.

At some point i lost hope that i can get a help from you. but really thanks.

Coming to your pbix, i tried all the dax and calculated columns you have given and yes i got the output.

But the issue is my table contains around 50lakhs of records.

So for now, i jus took a single date values(6/1/2018) and it is around 750 records.

After that, when i tried those dax's, the second dax, "END" i gota error as

I really didnt get whats the error about.

Then i filtered it out by few of the employee ids, and tried the same, then it worked.

i got the strength final values.

But i got lots of negative values.

Output:- 

Then i have checked The strength values are not as expected.

I ll be sharing you the data, please look into it whenever you got free time.

I hope, n i wish to see your magnificient support.

Thanks,

Mohan V

[v-juanli-msft]

Hi @Anonymous

The error is one column which is used in the formula is in text type, but we need it to be in date/time type.

I haven't recevice your data until now.

Best Regards

Maggie

[Anonymous]

@v-juanli-msft sent link in private message.
Please check once.

[Anonymous]

@v-juanli-msft could you please suggest me on this.

[Anonymous · Accepted solution]

I think i got the solution on my own.

I broke down the 24 hours into 30 min gap each.

 and i have written if condtion for all the durations and took only In employees count.

#"Added Custom1" = Table.AddColumn(#"Changed Type1", "Attendance", each if [#"Date-Time"] >= [BEGIN_DATE]&#time(0,0,0) and [#"Date-Time"] < [BEGIN_DATE]&#time(0,30,0) then "0-0:30" 
else
if [#"Date-Time"] >= [BEGIN_DATE]&#time(0,30,0) and [#"Date-Time"] < [BEGIN_DATE]&#time(1,0,0) then "0:30-1" 
else
if [#"Date-Time"] >= [BEGIN_DATE]&#time(1,0,0) and [#"Date-Time"] < [BEGIN_DATE]&#time(1,30,0) then "1-1:30" 
else
if [#"Date-Time"] >= [BEGIN_DATE]&#time(1,30,0) and [#"Date-Time"] < [BEGIN_DATE]&#time(2,0,0) then "1:30-2"
else
if [#"Date-Time"] >= [BEGIN_DATE]&#time(2,0,0) and [#"Date-Time"] < [BEGIN_DATE]&#time(2,30,0) then "2-2:30"
else
if [#"Date-Time"] >= [BEGIN_DATE]&#time(2,30,0) and [#"Date-Time"] < [BEGIN_DATE]&#time(3,0,0) then "2:30-3"
else
if [#"Date-Time"] >= [BEGIN_DATE]&#time(3,0,0) and [#"Date-Time"] < [BEGIN_DATE]&#time(3,30,0) then "3-3:30"
else
if [#"Date-Time"] >= [BEGIN_DATE]&#time(3,30,0) and [#"Date-Time"] < [BEGIN_DATE]&#time(4,0,0) then "3:30-4"
else
if [#"Date-Time"] >= [BEGIN_DATE]&#time(4,0,0) and [#"Date-Time"] < [BEGIN_DATE]&#time(4,30,0) then "4-4:30"
else
if [#"Date-Time"] >= [BEGIN_DATE]&#time(4,30,0) and [#"Date-Time"] < [BEGIN_DATE]&#time(5,0,0) then "4:30-5"
else
if [#"Date-Time"] >= [BEGIN_DATE]&#time(5,0,0) and [#"Date-Time"] < [BEGIN_DATE]&#time(5,30,0) then "5-5:30"
else
if [#"Date-Time"] >= [BEGIN_DATE]&#time(5,30,0) and [#"Date-Time"] < [BEGIN_DATE]&#time(6,0,0) then "5:30-6"
else
if [#"Date-Time"] >= [BEGIN_DATE]&#time(6,0,0) and [#"Date-Time"] < [BEGIN_DATE]&#time(6,30,0) then "6-6:30" 
else
if [#"Date-Time"] >= [BEGIN_DATE]&#time(6,30,0) and [#"Date-Time"] < [BEGIN_DATE]&#time(7,0,0) then "6:30-7"
else
if [#"Date-Time"] >= [BEGIN_DATE]&#time(7,0,0) and [#"Date-Time"] < [BEGIN_DATE]&#time(7,30,0) then "7-7:30"
else
if [#"Date-Time"] >= [BEGIN_DATE]&#time(7,30,0) and [#"Date-Time"] < [BEGIN_DATE]&#time(8,0,0) then "7:30-8"
else
if [#"Date-Time"] >= [BEGIN_DATE]&#time(8,0,0) and [#"Date-Time"] < [BEGIN_DATE]&#time(8,30,0) then "8-8:30"
else
if [#"Date-Time"] >= [BEGIN_DATE]&#time(8,30,0) and [#"Date-Time"] < [BEGIN_DATE]&#time(9,0,0) then "8:30-9"
else
if [#"Date-Time"] >= [BEGIN_DATE]&#time(9,0,0) and [#"Date-Time"] < [BEGIN_DATE]&#time(9,30,0) then "9-9:30"
else
if [#"Date-Time"] >= [BEGIN_DATE]&#time(9,30,0) and [#"Date-Time"] < [BEGIN_DATE]&#time(10,0,0) then "9:30-10"
else
if [#"Date-Time"] >= [BEGIN_DATE]&#time(10,0,0) and [#"Date-Time"] < [BEGIN_DATE]&#time(10,30,0) then "10-10:30"
else
if [#"Date-Time"] >= [BEGIN_DATE]&#time(10,30,0) and [#"Date-Time"] < [BEGIN_DATE]&#time(11,0,0) then "10:30-11"
else
if [#"Date-Time"] >= [BEGIN_DATE]&#time(11,0,0) and [#"Date-Time"] < [BEGIN_DATE]&#time(11,30,0) then "11-11:30"
else
if [#"Date-Time"] >= [BEGIN_DATE]&#time(11,30,0) and [#"Date-Time"] < [BEGIN_DATE]&#time(12,0,0) then "11:30-12"
else
if [#"Date-Time"] >= [BEGIN_DATE]&#time(12,0,0) and [#"Date-Time"] < [BEGIN_DATE]&#time(12,30,0) then "12-12:30"
else
if [#"Date-Time"] >= [BEGIN_DATE]&#time(12,30,0) and [#"Date-Time"] < [BEGIN_DATE]&#time(13,0,0) then "12:30-13"
else
if [#"Date-Time"] >= [BEGIN_DATE]&#time(13,00,0) and [#"Date-Time"] < [BEGIN_DATE]&#time(13,30,0) then "13-13:30"
else
if [#"Date-Time"] >= [BEGIN_DATE]&#time(13,30,0) and [#"Date-Time"] < [BEGIN_DATE]&#time(14,0,0) then "13:30-14"
else
if [#"Date-Time"] >= [BEGIN_DATE]&#time(14,00,0) and [#"Date-Time"] < [BEGIN_DATE]&#time(14,30,0) then "14-14:30"
else
if [#"Date-Time"] >= [BEGIN_DATE]&#time(14,30,0) and [#"Date-Time"] < [BEGIN_DATE]&#time(15,0,0) then "14:30-15"
else
if [#"Date-Time"] >= [BEGIN_DATE]&#time(15,00,0) and [#"Date-Time"] < [BEGIN_DATE]&#time(15,30,0) then "15-15:30"
else
if [#"Date-Time"] >= [BEGIN_DATE]&#time(15,30,0) and [#"Date-Time"] < [BEGIN_DATE]&#time(16,00,0) then "15:30-16"
else
if [#"Date-Time"] >= [BEGIN_DATE]&#time(16,00,0) and [#"Date-Time"] < [BEGIN_DATE]&#time(16,30,0) then "16-16:30"
else
if [#"Date-Time"] >= [BEGIN_DATE]&#time(16,30,0) and [#"Date-Time"] < [BEGIN_DATE]&#time(17,0,0) then "16:30-17"
else
if [#"Date-Time"] >= [BEGIN_DATE]&#time(17,00,0) and [#"Date-Time"] < [BEGIN_DATE]&#time(17,30,0) then "17-17:30"
else
if [#"Date-Time"] >= [BEGIN_DATE]&#time(17,30,0) and [#"Date-Time"] < [BEGIN_DATE]&#time(18,0,0) then "17:30-18"
else
if [#"Date-Time"] >= [BEGIN_DATE]&#time(18,00,0) and [#"Date-Time"] < [BEGIN_DATE]&#time(18,30,0) then "18-18:30"
else
if [#"Date-Time"] >= [BEGIN_DATE]&#time(18,30,0) and [#"Date-Time"] < [BEGIN_DATE]&#time(19,0,0) then "18:30-19"
else
if [#"Date-Time"] >= [BEGIN_DATE]&#time(19,00,0) and [#"Date-Time"] < [BEGIN_DATE]&#time(19,30,0) then "19-19:30"
else
if [#"Date-Time"] >= [BEGIN_DATE]&#time(19,30,0) and [#"Date-Time"] < [BEGIN_DATE]&#time(20,0,0) then "19:30-20"
else
if [#"Date-Time"] >= [BEGIN_DATE]&#time(20,00,0) and [#"Date-Time"] < [BEGIN_DATE]&#time(20,30,0) then "20-20:30"
else
if [#"Date-Time"] >= [BEGIN_DATE]&#time(20,30,0) and [#"Date-Time"] < [BEGIN_DATE]&#time(21,0,0) then "20:30-21"
else
if [#"Date-Time"] >= [BEGIN_DATE]&#time(21,00,0) and [#"Date-Time"] < [BEGIN_DATE]&#time(21,30,0) then "21-21:30"
else
if [#"Date-Time"] >= [BEGIN_DATE]&#time(21,30,0) and [#"Date-Time"] < [BEGIN_DATE]&#time(22,0,0) then "21:30-22"
else
if [#"Date-Time"] >= [BEGIN_DATE]&#time(22,00,0) and [#"Date-Time"] < [BEGIN_DATE]&#time(22,30,0) then "22-22:30"
else
if [#"Date-Time"] >= [BEGIN_DATE]&#time(22,30,0) and [#"Date-Time"] < [BEGIN_DATE]&#time(23,0,0) then "22:30-23"
else
if [#"Date-Time"] >= [BEGIN_DATE]&#time(23,00,0) and [#"Date-Time"] < [BEGIN_DATE]&#time(23,30,0) then "23-23:30"
else
if [#"Date-Time"] >= [BEGIN_DATE]&#time(23,30,0) and [#"Date-Time"] < [BEGIN_DATE]&#time(24,00,0) then "23:30-24"
else null),

And it worked for me.

[RMDNA]

@Anonymous,

Is the text in the Strength in Class column just there for extra clarity in your example, or do you need that to actually be part of the result?

[Anonymous]

@RMDNA Could you please suggest me on this.

I will be so thankful to you.

Thanks,

Mohan V

[Anonymous]

@RMDNA yes Its for your extra clarity.