Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
Solved! Go to 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.
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.
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.
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.
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.
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.
User | Count |
---|---|
128 | |
109 | |
100 | |
65 | |
62 |
User | Count |
---|---|
137 | |
115 | |
102 | |
71 | |
61 |