Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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
Solved! Go to 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
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))
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
Yes, replace CONCATENATEX with COUNTROWS.
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
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))
User | Count |
---|---|
50 | |
24 | |
18 | |
17 | |
14 |
User | Count |
---|---|
91 | |
86 | |
39 | |
25 | |
21 |