Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi All,
I am here again with a unique qeustion. I read through some of the old posts and it doesnt seem to solve my issue.
So i have a data set that has information on entities and whether the name of the entity has been changed or not.
For example
Parent Company | Entity Name | Valid From | Valid To |
LEGAL | LEGAL A | 12.31.1990 | 12.31.2000 |
LEGAL | LEGAL B | 01.01.2001 | 12.31.2010 |
LEGAL | LEGAL C | 01.01.2011 | 12.31.2020 |
I would like to create a filter where the user needs to manually enter the date (preferably a text input) and this needs to be the criteria for date filter.
Example - If the user enters 05.01.1998, i would like to show the the valid information during the time frame. The problem is we do not have data for every single day during the last 30 years and from a usability perspective the data slicers are not helping the cause.
Is there a way we could include a dynamic date input and use that information to filter the values in the page/table ?
Any guidance would be highly appreciated
Solved! Go to Solution.
Hi Selvarajen
Note that you type the date in the slicer or you can select it from the calendar menu.
It then indicates which rows are valid. You can then adapt this to just show the rows you want.
How to do it:-
Use the List.Date function to create a list of dates from 1990 to today.
Add a date slicer using this trick
https://youtu.be/zhWtU0DynCk
Then add this measure
Indicator =
--- get slicer date
VAR pickdate = MIN(Picklist[Date])
--- create a subset of the table where the pickdate is between the valid from/to range
VAR data_within_range =
FILTER(Facts, pickdate >= Facts[Valid From] && pickdate <= Facts[Valid To])
Return
--- Is the subset empty ?
INT(NOT(ISEMPTY(data_within_range)))
+ 0
Remember we are BI community voluntrees so please click the thumbs-up for me taking the effort to help you and then accept the solution if it works. Thank you !
@Selvarajen , refer if this blog on a similar topic can help. seem like you are looking for current employee kind of measure
Dear Amit.
Thanks for your message and the blog.
It is very insightful but unfortunately, our date range might be from 1980 to 2030 (50 years) which will be very hard to filter from.
That is one of the reasons i was looking for a more dynamic user input text filter that could be used.
Is there any other way the date filter could have a search option, that would also minimize the effort as a work around
Hi Selvarajen
Note that you type the date in the slicer or you can select it from the calendar menu.
It then indicates which rows are valid. You can then adapt this to just show the rows you want.
How to do it:-
Use the List.Date function to create a list of dates from 1990 to today.
Add a date slicer using this trick
https://youtu.be/zhWtU0DynCk
Then add this measure
Indicator =
--- get slicer date
VAR pickdate = MIN(Picklist[Date])
--- create a subset of the table where the pickdate is between the valid from/to range
VAR data_within_range =
FILTER(Facts, pickdate >= Facts[Valid From] && pickdate <= Facts[Valid To])
Return
--- Is the subset empty ?
INT(NOT(ISEMPTY(data_within_range)))
+ 0
Remember we are BI community voluntrees so please click the thumbs-up for me taking the effort to help you and then accept the solution if it works. Thank you !
@speedramps Thank you so much. Of course, if there is an option to give you an heart, i would definitely do that.
Your solution helped me fix the problem and also use it in multiple other requirements.
I appreciate you taking the time to help me out here.
Best Regards,
Selvarajen
Awe ... thank you Selvarajen, that is the nicest thing anyone has said to me on forum!
Glad my solution helped.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
95 | |
76 | |
65 | |
51 |
User | Count |
---|---|
146 | |
109 | |
106 | |
88 | |
61 |