Forum Discussion

Laur1987's avatar
Laur1987
Frequent Visitor
4 years ago
Solved

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 contai...
  • tamerj1's avatar
    tamerj1
    4 years ago

    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's avatar
    Laur1987
    4 years ago

    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's avatar
tamerj1
Community Champion
4 years ago

Hi Laur1987 
Please provide some sample data.

  • tamerj1's avatar
    tamerj1
    Community Champion
    4 years ago

    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's avatar
      Laur1987
      Frequent Visitor
      4 years ago

      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's avatar
        tamerj1
        Community Champion
        4 years ago

        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
    )