Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Ashwin
Frequent Visitor

Date and time input - slicer - fix time

Hello PBI Users,

 

Please help me if you know how to solve this:

 

I have a column which I imported using a direct query that has date and time values in a single column like this:

 

2018-07-10 4:01:36 PM

 

I want to use these values to let the user choose the date using a slicer with the date as input. For example, if the user selects 2019-01-01, I want to select all rows which have a date-time value between 2019-01-01 4:00:00 AM and 2019-01-02 4:00:00 AM. 

 

Another example: If I select 2020-01-27 - it should include results when 2020-01-27 4:00:00 AM <= DTM < 2020-01-28 4:00:00 AM

 

 

1 ACCEPTED SOLUTION

Hi @Ashwin ,

 

You will need a relationship between table2 and other visuals or use SELECTEDVALUE() function.

Please check the two visuals of this pbix, hopefully works for you.

 

Best Regards,

Jay

Community Support Team _ Jay Wang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Community Support Team _ Jay
If this post helps, then please consider Accept it as the solution
to help the other members find it.

View solution in original post

5 REPLIES 5
v-jayw-msft
Community Support
Community Support

Hi @Ashwin ,

 

Please check the following steps.

1# Create a calendar table.

Table 2 = CALENDARAUTO()

 2# Create a measure.

Measure = 
var a = FORMAT(SELECTEDVALUE('Table'[date]),"YYYYMMDDHHmmSS")
var b = FORMAT(SELECTEDVALUE('Table 2'[Date]),"YYYYMMDD")&"040000"
var c = FORMAT(DATEADD('Table 2'[Date],1,DAY),"YYYYMMDD")&"040000"
return
IF(NOT(ISFILTERED('Table 2'[Date])),1,IF(a>=b&&a<c,1,BLANK()))

3# Add the measure to visual level filter.

Result would be shown as below.

1.PNG2.PNG3.PNG

 

Best Regards,

Jay

Community Support Team _ Jay Wang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Community Support Team _ Jay
If this post helps, then please consider Accept it as the solution
to help the other members find it.

@v-jayw-msft 

Thank you, Jay. I just created a table and the measures. I selected table 2 and then when I check the list view I get dates along with the time field. How can I remove the time field, and, would selecting dates from this table filter the results in other visuals? 

Please help!

Hi @Ashwin ,

 

1# Select the column and click Modeling -> Format.

5.PNG

2# No, it won't, but you can use SELECTEDVALUE() function as below measures.

 

Best Regards,

Jay

Community Support Team _ Jay Wang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Community Support Team _ Jay
If this post helps, then please consider Accept it as the solution
to help the other members find it.

@v-jayw-msft 

 

Thank you, the format worked to change the list. However, selecting the dates on table 2 does not change the other visuals according to the date as any other slicer would do? Do you know how to make this work? Thank you again 🙂

Hi @Ashwin ,

 

You will need a relationship between table2 and other visuals or use SELECTEDVALUE() function.

Please check the two visuals of this pbix, hopefully works for you.

 

Best Regards,

Jay

Community Support Team _ Jay Wang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Community Support Team _ Jay
If this post helps, then please consider Accept it as the solution
to help the other members find it.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.