Forum Discussion
Hour Relative Filter
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.
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
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
5 Replies
Hi Anonymous ,
- 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
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.