Create a DAX that returns the title of the event within event schedule

[third_hicana]

Hi. I'm having a hard time for days thinking of how to create my DAX measure. I would highly appreciate your help.

I want to create a DAX that returns the title of the event if the start and end date of viewers fall within the range of time of the event schedule. The event schedule does not exist in my Power BI model. It is just a reference regarding the order of the online event. The Viewers table is the table I have in my data model. I wish to have this scenario/output.

For example:

Person A joined and left the online event between 7:00am to 8:00 am, therefore the output is 'Data Driven Decision'.

Person C, the output is 'Data Driven Decision', 'Litmus', 'Productivity', and 'Education' since it overlaps to 4 sessions of the online event.

I tried this DAX but it does not work for overalapping start time and end time of viewers based on event schedule.

Sessions Attended =

DDD_Viewers[start_time] >= TIME(07,00,00) &&
DDD_Viewers[end_time] < TIME(08,00,00) , "Data Driven Decision",

DDD_Viewers[start_time] >= TIME(08,00,00) &&
DDD_Viewers[end_time] < TIME(09,00,00) && DDD_Viewers[PK_Room] = "4" , "Litmus"
)

Thank you,

Regards,

Third

[third_hicana · Accepted solution]

Hi Ibendlin,

Thank you for your help.

A slight modification of what you've created.

Is there a chance that instead of Concatenated session titles attended by a person, the return is the COUNT of title attended?

So for example. FOr Person D, the return should be 4

[lbendlin · Accepted solution]

Yes, replace CONCATENATEX with COUNTROWS.

[lbendlin]

[third_hicana · Accepted solution]

Hi Ibendlin,

Thank you for your help.

A slight modification of what you've created.

Is there a chance that instead of Concatenated session titles attended by a person, the return is the COUNT of title attended?

So for example. FOr Person D, the return should be 4

[lbendlin · Accepted solution]

Yes, replace CONCATENATEX with COUNTROWS.

[third_hicana]

Hi @lbendlin 

Asking for your help again. Is there a chance to count the unique persons per event. It's like the reverse of what you've done. 

Example: 

Productivity has 3 unique persons which are Person C, D and E based on time in and time since they fall between 9am to 10am.

Appreciate your help. 

-Third

[lbendlin · Accepted solution]

Calculated column in 'Event Schedule' Table:

Person Count = 
var GS = GENERATESERIES(Round([Start]*1440,0),Round([End]*1440-1,0))
var ev = ADDCOLUMNS(Viewers,"o",COUNTROWS(INTERSECT(GENERATESERIES(ROUND([Enter]*1440,0),Round([Exit]*1440,0)),GS)))
return Countrows(FILTER(ev,[o]>0))

[third_hicana]

@lbendlin  Thank you very much