Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
I am attempting to create a report that all filters will react to and I am having some trouble.
My data set has two columns to pull off of -
start date/time & end date/time
I want to build something that I can use a date and time slicer to see if this record was "active" within this range
lets say I set my slicer to start date 1/9/2018 2:00PM to 1/10/2018 1:00PM, I would expect rows 1 & 2 to be the only two records in the report. This goes for all visuals - so lets say I did a visual of distinct count of numbers in the column I would get 2.
I tried something very similar but it never works the way I intend it to.
I have a date table that has no relationship to my main data table.
I wrote a measure that gets a count of rows on the main table and returns a number which is fitlered based on the min/max from the data table (which has its own slicer). I then put a filter on the visuals in the report for that mesaure and if greater than 0. It kind of works but not totally how I expect.
# | start date | end date |
1 | 1/9/2018 10:00 AM | 1/10/2018 8:00 AM |
2 | 1/10/2018 10:00 AM | 1/11/2018 8:00 AM |
3 | 1/11/2018 10:00 AM | 1/12/2018 8:00 AM |
4 | 1/12/2018 10:00 AM | 1/13/2018 8:00 AM |
5 | 1/13/2018 10:00 AM | 1/14/2018 8:00 AM |
6 | 1/14/2018 10:00 AM | 1/15/2018 8:00 AM |
7 | 1/15/2018 10:00 AM | 1/16/2018 8:00 AM |
8 | 1/16/2018 10:00 AM | 1/17/2018 8:00 AM |
9 | 1/17/2018 10:00 AM | 1/18/2018 8:00 AM |
Measure = CALCULATE ( COUNTROWS ( Table1 ), FILTER ( Table1, Table1[Startdate] > MIN ( 'Table'[Date] ) ) )
THIS Should work
Will this work on all visuals?
Yes, This will work on all visuals.
@thmonte do you want slicer date range against start date / time or also end date /time?
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
Now that I am thinking about it..
Maybe the slicer should just be a single date/time
I would expect that if I select 1/1/2019 8:00AM
I would expect any records where the slicer date falls between the start date/end date to be filtered out on the report.
Hi @thmonte ,
Do you means filter records which in selected range? If this is a case, you can't direct use slicer to achieve this.
You need to add new slicer with date field who not has relationship to original tables. Then write a measure to compare current row contents(start and datetime) with selected date and return result tag, drag this measure to visual level filter to filter non matched records.
Tag Measure = VAR _start = MAX ( Table[Start] ) VAR _end = MAX ( Table[End] ) RETURN IF ( _start IN ALLSELECTED ( Date[Date] ) || _end IN ALLSELECTED ( Date[Date] ), "N", "Y" )
Regards,
Xiaoxin Sheng
User | Count |
---|---|
93 | |
84 | |
78 | |
75 | |
66 |
User | Count |
---|---|
115 | |
105 | |
93 | |
65 | |
60 |