Count values between two times with 30 mins interval on X- Axis

[cv2qm]

Hi,

I am trying to count value between two intervals.

I wrote measure to calculate, it is working fine. But i needvalue on 30 min interval on X -axis.

If i create table in power bi, on which column i need to give relation?

Measure 2 =
CALCULATE (
    DISTINCTCOUNT ( 'Data table'[StudentID] ),
    FILTER (
        'Data table',
        'Data table'[BeginTime] <= MAX ( 'Data table'[BeginTime] )
            && 'Data table'[EndTime] >= MIN ( 'Data table'[EndTime] )
    )
)

Can anyone help me what i need to do to get count of values based on 30 mins interval values on X-axis.

[v-eachen-msft · Accepted solution]

Hi @cv2qm ,

You could create a new table with the following DAX:

New Table =
GENERATESERIES (
    MIN ( 'Table'[EndTime] ),
    MAX ( 'Table'[BeginTime] ),
    TIME ( 0, 30, 0 )
)

Then you will get a time column as 30 mins interval.

Now you could use this column as your x-axis. 

Count the ID refering to your original DAX:

Count1 =
CALCULATE (
    DISTINCTCOUNT ( 'Table'[StudentId] ),
    FILTER (
        'Table',
        'Table'[BeginTime] <= 'NewTable'[Value]
            && 'Table'[EndTime] >= 'NewTable'[Value]
    )
)

[Greg_Deckler]

OK, if I am understanding you correctly, you will want to create a disconnected table using GENERATESERIES for your x-axis. You will then likely need to do something along the lines of Open Tickets. https://community.powerbi.com/t5/Quick-Measures-Gallery/Open-Tickets/td-p/409364

If you can provide sample source data and such, can likely be more specific. Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

[cv2qm]

Thanks for quick response. 

Attached sample data. 

I am trying to get Count of students bewteen two times. 

I want to show line chart as 30 mins interval on x-axis with number of students in that 30 mins interval.

StudentId	BeginTime	EndTime
aan5tk	1000	1100
aan5tk	1000	1100
aan5tk	1000	1120
aan5tk	1130	1250
aan5tk	1410	1510
aan5tk	1815	1945
aan5tk	1815	1945
aan5tk	1540	1700
aan5tk	830	950
aan5tk	1410	1530
aan5tk	1000	1120
aca6w	1000	1100
aca6w	1410	1510
aca6w	830	950
aca6w	1300	1400
aca6w	1410	1530
aca6w	1540	1700
aca6w	1000	1100
aca6w	1000	1120
aca6w	850	950
aca6w	1000	1120
ada5tf	1000	1100
ada5tf	1000	1100
ada5tf	1000	1120
ada5tf	1410	1510
ada5tf	1540	1700
ada5tf	1540	1700
ada5tf	1130	1250
ada5tf	830	950
ada5tf	1410	1530
ada5tf	1000	1120
adb3qr	1000	1120
adb3qr	830	930

[v-eachen-msft · Accepted solution]

Hi @cv2qm ,

You could create a new table with the following DAX:

New Table =
GENERATESERIES (
    MIN ( 'Table'[EndTime] ),
    MAX ( 'Table'[BeginTime] ),
    TIME ( 0, 30, 0 )
)

Then you will get a time column as 30 mins interval.

Now you could use this column as your x-axis. 

Count the ID refering to your original DAX:

Count1 =
CALCULATE (
    DISTINCTCOUNT ( 'Table'[StudentId] ),
    FILTER (
        'Table',
        'Table'[BeginTime] <= 'NewTable'[Value]
            && 'Table'[EndTime] >= 'NewTable'[Value]
    )
)

[cv2qm]

Hi @v-eachen-msft,

Thanks for Reply.

Values are looking good. 

But some timings are missing on Axis. If data don't have some timmings. Then it's not showing on Axis. 

The main purpose of chart is to see good time to schedule anything.

Can you guide me how can i fix this. 

Below screenshot, 16:00 to 17:30 are missing. 

[ImkeF · Accepted solution]

Hi you can adjust the measure to that it returns 0 instead of BLANK: Count1 = VAR Result = CALCULATE ( DISTINCTCOUNT ( 'Table'[StudentId] ), FILTER ( 'Table', 'Table'[BeginTime] <= 'NewTable'[Value] && 'Table'[EndTime] >= 'NewTable'[Value] ) RETURN IF ( ISBLANK ( Result ), 0 , Result )

[cv2qm]

@ImkeF  Thank You! 

It's working fine. 

[cv2qm]

Thank you 😗!