Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

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 ...
Tips and Tricks
  • v-diye-msft's avatar
    v-diye-msft
    7 years ago

    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's avatar
    v-diye-msft
    7 years ago

    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

v-diye-msft's avatar
v-diye-msft
Community Support
7 years ago

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's avatar
    Anonymous
    Not applicable
    7 years ago

    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's avatar
      Anonymous
      Not applicable
      7 years ago

      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.