Need help with attendance in an hour chart

[sujatakaran]

Hello Community,

It has been really great to be part of powerbi community and I am really thankful for all the help that I get. It has helped me a lot in my project. 

There is yet another issue that I have and I am not able to figure out a way to do it.
I need to create a chart like this:

This chart is for number of people attending an online event and I have their unique ids, session id, in time and outtime.

Please help. Thank you in advance.

BR

Sujata

[amitchandak · Accepted solution]

@sujatakaran , see time table can help.

You have to join it with the time part. In case you have two time part , you may have use userelation

Time Part = [Date time].time

Use relation example : https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...

[amitchandak · Accepted solution]

@sujatakaran , see time table can help.

You have to join it with the time part. In case you have two time part , you may have use userelation

Time Part = [Date time].time

Use relation example : https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...

[sujatakaran]

Hi @amitchandak 

Thank you for your reply. 
I created the time table and connected these two tables with time:

Created a measure as per this:

Active_Attendees = CALCULATE(COUNTx(FILTER('Webinar Attendees & Registrants','Webinar Attendees & Registrants'[Joine_Time]<=max('Time'[Actual Time]) && (ISBLANK('Webinar Attendees & Registrants'[Leav_Time]) || 'Webinar Attendees & Registrants'[Leav_Time]>max('Time'[Actual Time]))),('Webinar Attendees & Registrants'[Email Address])),CROSSFILTER('Webinar Attendees & Registrants'[Joine_Time],'Time'[Actual Time],None))

and 

Left_Attendees = CALCULATE(COUNT('Webinar Attendees & Registrants'[Email Address]),USERELATIONSHIP('Webinar Attendees & Registrants'[Leav_Time],'Time'[Actual Time]),not(ISBLANK('Webinar Attendees & Registrants'[Leav_Time])))

It doesn't seem to be working. I am pretty naive in Powerbi so not sure what am i doing wrong.

Thank you for all your help.

[sujatakaran · Accepted solution]

Hello, This is resolved. 
@amitchandak I took some help from the HR analytics blog and then built on that.
I created a count of active attendees in a given time :

Joined_Attendees = CALCULATE(COUNT('Webinar Attendees & Registrants'[Email Address]),USERELATIONSHIP('Webinar Attendees & Registrants'[Joine_Time],'Time'[Actual Time]) )

Then I created count of left attendees in a given time :

Left_Attendees = CALCULATE(COUNT('Webinar Attendees & Registrants'[Email Address]),USERELATIONSHIP('Webinar Attendees & Registrants'[Leav_Time],'Time'[Actual Time]),not(ISBLANK('Webinar Attendees & Registrants'[Leav_Time])))

and then cacluated the present attendees field :

Present Attendees =

CALCULATE (

[Active_Attendees],

FILTER ( ALL ('Time' ),'Time'[Actual Time] <= MAX ( 'Time'[Actual Time] ) )

)

- CALCULATE (

[Left_Attendees],

FILTER ( ALL ( 'Time'), 'Time'[Actual Time] <= MAX ('Time'[Actual Time] ) )

)

And the simple drag down line chart .

Thank you so much for you help.

[amitchandak]

@sujatakaran , Good that it helped. Kudos to you.