Get chart using DAX

[Laur1987]

I need to get a chart like from Excel. I need to count the minutes between "Leave Time" and "Join Time" but considering that "Join time in the 14:05 rowcontain count from 14:04, so "14:11" row contain count from "14:10, 14:09, 14:08, 14:07, 14:06, 14:05, 14:04" and so on. I don't know if I am explaning very well. Thanks! In conclusion I need to see the range of users online on a platform.

@sturlaws@tamerj1 

[tamerj1 · Accepted solution]

Hi @Laur1987 

Thank you for the clarifications. Please refer to the sample file with the solution 

https://we.tl/t-zzBE0gh6YV

Count = 
VAR CurrentJoinTime = MAX ( '81099122685_-_Attendee_Report'[Join Time] )
RETURN
    CALCULATE ( 
        SUMX (
            '81099122685_-_Attendee_Report',
            VAR JoinTime = '81099122685_-_Attendee_Report'[Join Time]
            VAR LeaveTime = '81099122685_-_Attendee_Report'[Leave Time]
            RETURN
                IF ( 
                    CurrentJoinTime >= JoinTime && CurrentJoinTime <= LeaveTime,
                    1,
                    0
                )
        ), 
        REMOVEFILTERS( '81099122685_-_Attendee_Report' ),
        '81099122685_-_Attendee_Report'[Join Time] <= CurrentJoinTime
    )

[Laur1987]

I attached sample data. @tamerj1 

https://app.powerbi.com/links/YUZTSW0Jti?ctid=8a2108dc-fa7a-409d-b22f-fea2c3df61f6&pbi_source=linkSh... 

Thanks!

[tamerj1]

The link is not working. Would you please upload to WeTransfer, DropBox, OneDrive or any similar and share the link?

[tamerj1]

Hi @Laur1987 
Please provide some sample data.

[Laur1987]

https://www.dropbox.com/s/79xlcc8ed9rt43g/Attende.pbix?dl=0

@tamerj1 

[tamerj1]

@Laur1987 
Sory for the lte response. 
Sorry I did not understand what is required. What are you trying to count? Please explain whith one example based on the given sample data. Thank you

[Laur1987]

I need to count how many users are logged in by intervals of time. I supposed that User A joined at 14:04. User B at 14:05, User C joined at 14:10. That means that in my chart I will have: at 14:04 I have one user online, at :14:05 I have 2 users online, (at 14:10 I will have 8 users online... Because I add the users that are already online before 14:10 for example.)

See the below example from Excel: I used a lot of formulas and sheets to have this result.

@tamerj1Thanks!

[tamerj1 · Accepted solution]

Hi @Laur1987 

Thank you for the clarifications. Please refer to the sample file with the solution 

https://we.tl/t-zzBE0gh6YV

Count = 
VAR CurrentJoinTime = MAX ( '81099122685_-_Attendee_Report'[Join Time] )
RETURN
    CALCULATE ( 
        SUMX (
            '81099122685_-_Attendee_Report',
            VAR JoinTime = '81099122685_-_Attendee_Report'[Join Time]
            VAR LeaveTime = '81099122685_-_Attendee_Report'[Leave Time]
            RETURN
                IF ( 
                    CurrentJoinTime >= JoinTime && CurrentJoinTime <= LeaveTime,
                    1,
                    0
                )
        ), 
        REMOVEFILTERS( '81099122685_-_Attendee_Report' ),
        '81099122685_-_Attendee_Report'[Join Time] <= CurrentJoinTime
    )

[Laur1987 · Accepted solution]

Many thanks! You saved me! 😄 Can you tell how I can do to shrink time in chart? In this moment I see at half hour. I want to see at 5-10 minute the chart.  @tamerj1 

[tamerj1]

@Laur1987 
I don't know how to change the sacle. I advise you to post another question in the Desktop Forum. 
I hope I anwered your query correctly. If so kindly consider marking may reply as accepted solution.

Thank you