Latest Enterprise DNA Initiatives

Calculating Time metrics of the students

DAX Calculations
#1

I am trying to calculate different measures for TIME calculation. The file contains the joining and leaving by students several times in the online class.

Issue:
The issue is to calculate the summing up Total Time during a lecture. Also the Total Time (Hours, Minutes) during the whole semester. Also, there is need to count No. of students at any specific time (Minute granularity).

Now, I got the following graph by taking Live Data in the model.

image
image1299Ă—542 22.1 KB

I used TIME and Date Table for this purpose.
Model Data.xlsx (44.1 KB)

The snapshot of Model view is attached here.

image
image943Ă—777 43.4 KB

#2

Hi @Tanzeel , we aim to consistently improve the topics being posted on the forum to help you in getting a strong solution faster. While waiting for a response, here are some tips so you can get the most out of the forum and other Enterprise DNA resources.

  • Use the forum search to discover if your query has been asked before by another member.
  • When posting a topic with formula make sure that it is correctly formatted to preformatted text </>.image
  • Use the proper category that best describes your topic
  • Provide as much context to a question as possible.
  • Include the masked demo pbix file, images of the entire scenario you are dealing with, screenshot of the data model, details of how you want to visualize a result, and any other supporting links and details.

I also suggest that you check the forum guideline How To Use The Enterprise DNA Support Forum. Not adhering to it may sometimes cause delay in getting an answer.

Please also check the How To Mask Sensitive Data thread for some tips on how to mask your pbix file.

#3

Hi @Tanzeel, we’ve noticed that no response has been received from you since the 27th of December. We just want to check if you still need further help with this post? In case there won’t be any activity on it in the next few days, we’ll be tagging this post as Solved. If you have a follow question or concern related to this topic, please remove the Solution tag first by clicking the three dots beside Reply and then untick the checkbox. Thanks!

#4

I did try some preparations to develop logic to solve this issue.

image
image1402Ă—683 252 KB

I think that I have to find

1- Total Lecture Time by taking Min and Max values of any particular day and taking their differences.
2- Lecture Time by student = Active Time - Idle Time
3- I developed a simple logic for each student in every class as mentioned 1 & 2.

I need verification that I am at right path.

#5

@Tanzeel, this sounds as if you are started in the right direction.
In order for anyone to be able to really help with troubleshooting this type of question, a PBIX file mockup of your model would be helpful.

Often these questions require not only a knowledge of the measures built, but also the data structure & model of the report (as mentioned in the EnterpriseDNA post immediately below your first comment in this thread).

#6

Actually, I attached Excel File. However, a sample pbix file is also attached. EDNA sample.pbix (1.2 MB) Model Data.xlsx (106.4 KB)

I am trying to simplify the logic in Power Query.

Logic:

-First, find the class start and class end by taking Min and Max on the TIME Column for any particular day.

-Identify that when a student joins the class and leaves the class. In case of repeating this activity also count the total attempts.

-Number of attempts of each student to join the class.

-Find the total span of joining and leaving the class.

-What is the Head count at any particular instant?

-Total Time per day of any student in the class.

-Average Time of the student in the class.

-Average Lecture Time in each class.

-Total semester time of any student attending the class.

-Total Lecture Time in the Semester.

-How much time a student missed the class in any lecture.

#7

Seeing your model has made a difference - here are some questions

  1. I see no measures in your existing model, not a sum of the lecture time or even students in attendance - so are we starting from an absolute blank? Any measures you are currently using will help me to have something to build from.
  2. Can I assume that your Index on the Class Time data is a Class ID? (this makes a difference in calculating the original lecture time)
  3. I don’t see anything in the Class Time to indicate which student we are talking about (or is that was the Index is indicating)?

And, here are some suggestions:

  • Your date table was not marked as Date, this is always going to be a recommendation, to prevent bloat in your report (otherwise all date columns are going to generate their own date table)
  • Assuming your data does not have a separate class table (having the complete lecture time, etc), I would work on building one.
  • Assuming your data does not have a separate student table (which I suspect it does and you did not want to include to avoid release of sensitive data), again you need to work on building one. Even including one that doesn’t have name info would be useful here.
  • Both student and class data need to be connected to your Class Time table.
  • There are a lot of extra columns in your time table - are you going to use those in the final model? If not, consider trimming what you don’t need.
1 Like
#8

Hi @Tanzeel, did the response provided by @Heather help you solve your query? If not, how far did you get and what kind of help you need further? If yes, kindly mark the thread as solved. Thanks!

#9

Hi @Tanzeel, we’ve noticed that no response has been received from you since the 30th of December. We just want to check if you still need further help with this post? In case there won’t be any activity on it in the next few days, we’ll be tagging this post as Solved. If you have a follow question or concern related to this topic, please remove the Solution tag first by clicking the three dots beside Reply and then untick the checkbox. Thanks!

#10

A response on this post has been tagged as “Solution”. If you have a follow question or concern related to this topic, please remove the Solution tag first by clicking the three dots beside Reply and then untick the check box. Thanks!