Forum Discussion
Filtering a table based on a selected value in a date table
Hi,
I have a UserTable table with two dates, ValidFrom and ValidTo, and I want to only to display records that are valid on a certain date selected by a user. I have a Date Table that has no relationship with the UserTable. I want to get the user to select a single date, and use that date as part of the calculated column to say whether it should be included or not. I have created a measure "Date_Filter" in the UserTable which gets the SelectedValue from the date table. This works and I can get it to display correctly on the page. However when I use it as part of an if statement on a column in the User Table to see if the date is less than the ValidTo and greater than the ValidFrom it does not work, as the measure does not pull a value through to be used in the column. I have tried creating a Variable in the column for the selectedvalue but it comes through blank.
Any help would be appreciated.
Many Thanks
- Many Thanks, that would work if I was just looking at totals, but I am needing to show the details rather than totals from measures. I have found a workaround, in that the user has to put the same date in twice, using two date filters, one before and one after on the Date From and Date To. This allows me to achieve what I needed although not the best solution for the user.
2 Replies
BAR_kevin , Please find the file where I have filtered HR data that have joined with the Date table
In case one join is active you can follow these, else follow attcahed file
Power BI: HR Analytics - Employees as on Date : https://youtu.be/e6Y-l_JtCq4
https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-trend/ba-p/882970
Power BI HR Active Employee Tenure Bucketing, and Hired, Terminated, and Active employees: https://youtu.be/fvgcx8QLqZU- Many Thanks, that would work if I was just looking at totals, but I am needing to show the details rather than totals from measures. I have found a workaround, in that the user has to put the same date in twice, using two date filters, one before and one after on the Date From and Date To. This allows me to achieve what I needed although not the best solution for the user.
