How to find the activity time from Fact table

[Tanzeel]

I am struggling to find the various Time based measures in this table where column values are unpivoted. I want to calculate various measures as mentioned. 

-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.

-Toal Lecture Time in the Semester.

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

Preferablly, I want the DAX solution. Some solutions could be in power query as by first pivot the columns and arranging them in Joined Before, Joined, and Left categories regarding Timestamps in separate column[s]. My focus is to treat as Fact table. However, I have a little bit DAX knowledge. Please guide...

[Anonymous]

Hi @Tanzeel,

It sounds like a common date range analysis requirement, I'd like to suggest you take a look at the following link 'start date', 'end date' part if they suitable for your scenario:

Before You Post, Read This 

Regards,

Xiaoxin Sheng

[AllisonKennedy]

@Tanzeel  If you're wanting these in the report output then you can work simply on measures, since you want them displayed in the the visuals.

[Tanzeel]

@AllisonKennedy Thank you. Should I use Variables for complete logic or work on simply on measures?

[AllisonKennedy]

@Tanzeel My suggestion would be to leave the columns unpivoted and use Filter DAX functions to create the measures you need. So for example, 

Count Attempts to Join = SUMX(FILTER('Live Attendance', 'Live Attendance'[User Action] = "Joined"), 'Live Attendance'[Presence])

Then put that in a visual with StudentID in Rows or Axis.

You can use similar structure for many of the other measures, or you can use CALCULATE function if you prefer.

[Tanzeel]

Thanks... I think Measure is somehow good option. I have built the model as in snapsot-1 and tried to develop the graph as snapshot-2. Should I work on unpivoted columns based table or should make it pivoted to get separate columns?

[lbendlin]

Here's the preference for calculations: Do them as far upstream as possible, but not farther.  This means that Power Query is better than a calculated column which in turn is better than a measure.

In your scenario you have atomic entities - students, days, courses.  For each of your questions listed above you want to check if the answer to these questions can be influenced by any report filters. If the answer is no then a calculated column is perfectly good enough (if you don't like Power Query). If the answer is yes then you _really_  need a measure.

Use a calendar table in your data model, and add a column to your fact table that adds a date representation of your timestamp data.  That will help you when you then use the standard date computation functions in DAX.