Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Laur1987
Frequent Visitor

Get chart using DAX

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 

2.png3.png

2 ACCEPTED SOLUTIONS

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
    )

1.png

View solution in original post

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 23.png

View solution in original post

9 REPLIES 9
Laur1987
Frequent Visitor

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

tamerj1
Super User
Super User

Hi @Laur1987 
Please provide some sample data.

@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

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.

3.png

@tamerj1Thanks!

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
    )

1.png

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 23.png

@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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors