Hour Relative Filter

[Anonymous]

Hi Everyone ,

hoping someone might have an idea on how to do this as its currently got me stumped in PBI.

Have an incrementing data set of values of which includes a datetime value.

Interested in Selecting the data back (via a single check box) by providing a filter of say;

< 3 hour,

< 6 hour,

< 12 hours,

< 24 hrs checkboxes

 (no sliders).

For example current time is 10:05pm and I select the 3 hour check box I get that last 3 hours worth of data, 6 hour that last 6 hours of data etc.

[v-diye-msft · Accepted solution]

Hi @Anonymous 

I’m not calculating the counts of columns, the total volume could be removed in format pane. I’m filtering the actual table by rows, not sure where confused you. Kindly share your sample data and excepted result to me if you don't have any Confidential Information. Please upload your files to One Drive and share the link here.

I attached the pbix here for your reference: https://wicren-my.sharepoint.com/:u:/g/personal/dinaye_wicren_onmicrosoft_com/EaTca9zvCA9EsnLH6ksyJDwBZi3I9-YNDrtfsR_RHns5mA?e=9ubbIv 

Best regards,

Dina Ye

[v-diye-msft]

Hi @Anonymous ,

1. I’ve created a sample and added the measures below:

latest 3 hours = var Hourdiff = HOUR([NOW])-HOUR(MAX(Table1[Datetime]))
Return
IF(Hourdiff>=0&&Hourdiff<3&&DAY([NOW])=DAY(MAX([Datetime])),1,IF(HOUR([NOW])<3&&Hourdiff>=0&&Hourdiff<3&&DAY([NOW])-DAY(MAX([Datetime]))<=1,1,0))

latest 6 hours = var Hourdiff = HOUR([NOW])-HOUR(MAX(Table1[Datetime]))
Return
IF(Hourdiff>=0&&Hourdiff<6&&DAY([NOW])=DAY(MAX([Datetime])),1,IF(HOUR([NOW])<6&&Hourdiff>=0&&Hourdiff<6&&DAY([NOW])-DAY(MAX([Datetime]))<=1,1,0))

latest 12 hours = var Hourdiff = HOUR([NOW])-HOUR(MAX(Table1[Datetime]))
Return
IF(Hourdiff>=0&&Hourdiff<12&&DAY([NOW])=DAY(MAX([Datetime])),1,IF(HOUR([NOW])<12&&Hourdiff>=0&&Hourdiff<6&&DAY([NOW])-DAY(MAX([Datetime]))<=1,1,0))

latest 24 hours = var Hourdiff = HOUR([NOW])-HOUR(MAX(Table1[Datetime]))
Return
IF(Hourdiff>=0&&Hourdiff<24&&DAY([NOW])=DAY(MAX([Datetime])),1,IF(HOUR([NOW])<24&&Hourdiff>=0&&Hourdiff<=24&&DAY([NOW])-DAY(MAX([Datetime]))<=1&&HOUR([NOW])<HOUR(MAX([Datetime])),1,0))

2. Create the new table with 1 column listed below using for slicer:

3. Add the measure working for slicer:

Values in the hours = IF(SELECTEDVALUE(Table2[Column1])="Lastest 3 hours",MAXX(FILTER(table1,[latest 3 hours]=1),[Value]),IF(SELECTEDVALUE(Table2[Column1])="Latest 6 hours",MAXX(FILTER(table1,[latest 6 hours]=1),[Value]),IF(SELECTEDVALUE(Table2[Column1])="Latest 12 hours",MAXX(FILTER(Table1,[Latest 12 hours]=1),[Value]),IF(SELECTEDVALUE(Table2[Column1])="Latest 24 hours",MAXX(FILTER(table1,[latest 24 hours]=1),[Value]),MAX([Value])))))

Best regards,

Dina Ye

[Anonymous]

Hi Dina,

thankyou so much for taking the time and providing me a way forward. It really had be baffled on on to try to do this.

Ill try the setup and let you know how I go.

thanks again

[Anonymous]

Hi Dina,

finally got back to this tonight and I see how you have approached the problem. However I was after the actual rows to be filtered in the table not the count as expressed in your solution. Everything else Ive set up ( and have made a few mods ) but am struggling to get over the last hurdle with the returning the rows selected in those time periods.

[v-diye-msft · Accepted solution]

Hi @Anonymous 

I’m not calculating the counts of columns, the total volume could be removed in format pane. I’m filtering the actual table by rows, not sure where confused you. Kindly share your sample data and excepted result to me if you don't have any Confidential Information. Please upload your files to One Drive and share the link here.

I attached the pbix here for your reference: https://wicren-my.sharepoint.com/:u:/g/personal/dinaye_wicren_onmicrosoft_com/EaTca9zvCA9EsnLH6ksyJDwBZi3I9-YNDrtfsR_RHns5mA?e=9ubbIv 

Best regards,

Dina Ye

[v-diye-msft · Accepted solution]

Hi @Anonymous 

If my above post helps, could you please consider Accept it as the solution to help the other members find it more quickly. thanks!

Best regards,

Dina Ye