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 

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
Frequent Visitor
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

    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