topic Re: Create a DAX that returns the title of the event within event schedule in DAX Commands and Tips

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

third_hicana — Thu, 09 May 2024 15:54:38 GMT

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

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

lbendlin — Fri, 10 May 2024 00:51:30 GMT

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

third_hicana — Fri, 10 May 2024 05:11:45 GMT

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

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

lbendlin — Fri, 10 May 2024 11:33:29 GMT

Yes, replace CONCATENATEX with COUNTROWS.

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

third_hicana — Mon, 13 May 2024 10:28:28 GMT

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

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

lbendlin — Mon, 13 May 2024 20:39:54 GMT

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

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

third_hicana — Tue, 14 May 2024 02:25:44 GMT

lbendlin Thank you very much