Design a button which gives last 3 hours and last 6 hours data - Power BI Desktop

[Pooja_Pandya]

Hi All, 

I am working with below dataset where I want to create two buttons which should return last 3 hours data and last 6 hours data. I need help for creating buttons and configuring it with dataset so when I hit last 3 hours button it should return total creane counts for last 3 hours and when I hit last 6 hours button it should return last 6 hours total crane counts .
Dataset:

Design:

Any help is appreciated.

[Pooja_Pandya · Accepted solution]

I found a solution for my question. Please follow below.
Solution:
1. Create a table by writing below DAX :

Period =

VAR last3hrs = ADDCOLUMNS(CALCULATETABLE('Your Table',DATEDIFF('Your Table'[date],now(),MINUTE)<=180),

"In the last", "Last 3 hours")

VAR last6hrs = ADDCOLUMNS(CALCULATETABLE('Your Table',DATEDIFF('Your Table'[date],NOW(),MINUTE)<=360),

"In the last","Last 6 hours")

var SelectAll = ADDCOLUMNS(CALCULATETABLE('Your Table',DATEDIFF('Your Table'[date],NOW(),MINUTE)<=1440),

"In the last","Select All")

return

UNION(last3hrs,last6hrs,SelectAll)

2. Create another table (for Button as a slicer) from "Home">"Enter Data" as below
      

3. Provide a relationship between Period Table and Button Table that we created as above.
    

4. Go to visual Pane> 1. Select Table visual and add field that you would like to display, 2. Select slicer from Visual Pane and add Value, make it horizontal.

It will look like below:

You're good to Go. 
 

[amikm]

@Pooja_Pandya ,
Create a table with two rows: 3 Hours and 6 hours and use that as a slicer in your Visual ( make it horizontal)

Then create a two-measure that will show the count of Crane and then using IF condition and SELECTEDVALUE, you can use these measure and as per User Selection count will change as per DAX logic

[Pooja_Pandya]

I created a new table with values Last 3 hours and Last 6 hours. Created two measures which calculates total count for Last 3 hours and Last 6 hours like below:

Last 3 hours Crane counts = COUNT(Trail[TotalCount])

Last 6 hours Crane counts = COUNT(Trail[TotalCount])

Should I create another measure or calsulated column with IF condition and selected values?

[lbendlin]

"Last 3 hrs"  based on what? The maximum timestamp in your data, or the actual current time of the user looking at the report?  For the latter you can create relative time filters, bookmark them, and then tie buttons to the bookmarks.

[Pooja_Pandya]

Last 3 hours data = In respect to current time stamp whatever result set I have in my dataset should return data from current time stamp to past 3 hours. Eg: Let's say 9:52 am is current time stamp. when I press last 3 hours button, it should return data  for crane number and total crane moves within the time range of 9:52 am to 6:52am.  So basically button should link with current timestamp and based on that it should return respective result set.

[lbendlin]

Have you considered using the filter pane for this instead, with a relative time filter?

[Pooja_Pandya]

Yes, it's not working in my case.

[Pooja_Pandya]

I tried to follow below link but no success 
https://www.youtube.com/watch?v=hDopw1mPlrU

[lbendlin]

I meant the built-in filter pane.

[Pooja_Pandya]

Yes, I tried inbuilt filter pane but User want something with Button or slicer so when user press button, would be able to see last 3 hours,last 6 hours numbers based on current time stamp. 

[lbendlin]

You can tie a button to a bookmark which itself can be tied to a filter pane setting.

[Pooja_Pandya]

@lbendlin could you please share any link/video for reference?

[lbendlin]

power bi button bookmark filter at DuckDuckGo

[Pooja_Pandya]

@lbendlin I designed 2 buttons with the use of bookmarks. could u please help how should I include/configure filter with these two buttons?

[lbendlin]

Set the relative time filter to three hours and then update the first bookmark. Set the filter to six hours and update the second bookmark.

[Pooja_Pandya]

I tried to apply filters with button and bookmarks but returns wrong data. It's strage that Last 3 hours filter returns no value and Last 6 hours returns correct value using same concept.
Result for Last 6 hours :

Result for Last 3 hours:

[lbendlin]

how is the data fed into your data source? is it real-time?

[Pooja_Pandya]

yes, It's real time.  

[lbendlin]

It works as designed. However if your dataset was refreshed more than three hours ago then the first button will not give any results. Consider switching to Direct Query mode.

[Pooja_Pandya]

No luck with Direct Query Mode too:(