Display values in 10 mins interval

[v-shex-msft]

Hi @Anonymous,

I'd like to suggest you add a calculate column to convert time value as specific range. Then you can create a time interval table and build relationship between above column and time interval table column.

Table sample:

Time interval =
SELECTCOLUMNS (
    GENERATESERIES ( TIME ( 0, 0, 0 ), TIME ( 23, 59, 59 ), TIME ( 0, 15, 0 ) ),
    "Start", [Value],
    "End", [Value] + TIME ( 0, 14, 59 ),
    "Index", (
        HOUR ( [Value] ) * 60
            + MINUTE ( [Value] )
    )
        / 15,
    "Rolling interval", [Value]
)

After these steps, you can use time interval column(rolling interval) and original table amount to create visual graph.

Regards,

Xiaoxin Sheng

[Anonymous]

Hello @v-shex-msft  Sorry for replying late it was a Christmas break
I didn't understand your solution and it is not me who accepted it as a solution 🙂
How can I create a calculated column to convert time value as a specific range? I have created a calculated column, time interval(time2) and made a relationship between rolling interval and time2 column, but it didn't solve my problem(it still shows me 6 body values in 10 mins) and when I try to use rolling interval on a shared axis the other columns from the original table doesn't display value on the visualization(For example, Airflow and body value)
Below is the time interval column I created

time2 = DATE(YEAR(CombinedTable[Date]),MONTH(CombinedTable[Date]),DAY(CombinedTable[Date]))+TIME(HOUR(CombinedTable[Date]),FLOOR(MINUTE(CombinedTable[Date]),10),0)

[v-shex-msft]

HI @Anonymous,

Can you please share a sample pbix file with some sample data for test?

Regards,

Xiaoxin Sheng

[Anonymous]

Hi @v-shex-msftI don't have the permission to upload .pbix files

[v-shex-msft]

HI @Anonymous,

You can upload sample file to onedrive or google drive and paste share link here..

Regards,

Xiaoxin Sheng

[Anonymous]

Hi @v-shex-msft

To revise my question
On the visualization, The body column shouldn't show values more than 3, because we only have 3 body sensors in the room. Whenever there is someone in the room(near to the sensor) the body sensor can send values more than once in 10 mins interval , So whenever the body sensor sends a value(1) more than once in 10 mins interval I want to take only 1 value for each body sensor ABC, DEF, GHI, So I can see how many people were in the room in 10 mins interval.

I hope it's clear.

https://drive.google.com/open?id=1bjP99zYKt0H2HezA5r4b6MzkXrsw8NOs

[Anonymous]

@v-shex-msftdoes the file open for you?

[v-shex-msft]

Hi @Anonymous,

You can try to use following measure which use firstnonblank function to filter these same value based on current type and time interval:

First BodyValue =
CALCULATE (
    FIRSTNONBLANK ( Append1[BodyValue], [BodyValue] ),
    FILTER (
        ALLSELECTED ( Append1 ),
        [Type] = MAX ( Append1[Type] )
            && [TimeInterval] = MAX ( Append1[TimeInterval] )
    )
)

Regards,

Xiaoxin Sheng

[Anonymous]

@v-shex-msft

Thanks for your reply!

This only shows 1 body value at 10:40 and 10:50, but it was supposed to show 2 body values for each, because between the time interval 10:40-10:50 we have 2 body values from Sensor type ABC and GHI and between the time interval 10:50-11:00 we have 2 body values from sensor type ABC and GHI.
If there is more than 1 value in 10 mins interval it takes only 1 of them from each type and adds them together so that I know how many people were in the room based on the value of each body sensors(Type).

The result I am expecting to see is like below

[v-shex-msft]

Hi @Anonymous,

>>If there is more than 1 value in 10 mins interval it takes only 1 of them from each type and adds them together so that I know how many people were in the room based on the value of each body sensors(Type).

Nope, line and clustered column chart not support to group line field measure to multiple graph.  You can change it to column field and add column series so power bi can analyze it as multiple bars.

Regards,

Xiaoxin Sheng

[themistoklis]

@Anonymous

This link will help you.