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.
I have a table that contains the below columns:
Employee
I would like to create a slicer that can show me all employees that were active between two dates, is that possible without enumerating a date column for each day the user was active? That would really blow up the size of my report..
Solved! Go to Solution.
Hi @Ashish_Mathur,
Thank you for all your help. I ended up enumerating all the dates as you suggested and I really didn't experience a performance hit.
For reference, here's how I enumerated my column including the Power Query (M) used:
if [Start Date]<>null and [End Date]<>null and [End Date]>[Start Date] then List.Dates([Start Date],Duration.TotalDays([End Date]-[Start Date]),#duration(1,0,0,0)) else if [Start Date]<>null then List.Dates([Start Date],Duration.TotalDays(Date.From(DateTime.LocalNow())-[Start Date]),#duration(1,0,0,0)) else {null}I have some contingencies on my data that had to be checked. I had to make sure there was actually an [End Date] and that it was later than the [Start Date]. In my data, the [End Date] can be earlier than the [Start Date] because we tend to rehire employees, which updates the [Start Date]. If they are currently still active then I'm using todays current date as the end date.
Hi,
I doubt you will be able to get a complete solution without enumerating a date column using the Query Editor. In the Query Editor, one can fairly simply create that date column.
Would you mind explaining how to go about that?
Would it be better/possible to enumerate the dates so that it only enumaretes by quarter or month? That way I'm not creating a row for every day over 5 - 10 years.
Hi,
You'd be better off enumerating the column date wise becuase that will allow you to use Time Intelligence functions. You may read my Blog post where i used the enumeration technique in POwer QUery i.e. Query Editor - Split total patient hospitalisation days into multiple months.
Hope this helps.
Hi @Ashish_Mathur,
Thank you for all your help. I ended up enumerating all the dates as you suggested and I really didn't experience a performance hit.
For reference, here's how I enumerated my column including the Power Query (M) used:
if [Start Date]<>null and [End Date]<>null and [End Date]>[Start Date] then List.Dates([Start Date],Duration.TotalDays([End Date]-[Start Date]),#duration(1,0,0,0)) else if [Start Date]<>null then List.Dates([Start Date],Duration.TotalDays(Date.From(DateTime.LocalNow())-[Start Date]),#duration(1,0,0,0)) else {null}I have some contingencies on my data that had to be checked. I had to make sure there was actually an [End Date] and that it was later than the [Start Date]. In my data, the [End Date] can be earlier than the [Start Date] because we tend to rehire employees, which updates the [Start Date]. If they are currently still active then I'm using todays current date as the end date.
Okay, I created the list as requested, when I tried creating an Advanced Visual Level filter where item contains '3/5/2012' example, nothing is returning, nothing is returning for any dates, how do I fix this ? Thanks, I checked the values in the query editor, and I know they are correct at least, but filter is not working
You are welcome.
the link does not work anymore
I would use a disconnected calendar table. Basically, duplicate your calendar table and have a min and max date measure on that duplicate table. Do not connect that table to anything. Then, use the date from that disconnected table as your slicer, and reference the min and max date in your "active employee" measure. As you adjust the date range on your slicer, the min and max date will change, but your main data table connected to your main calendar table will not be filtered by the slicer (which is what you want). Now, those min and max dates are simply values for you to use in your logical test to deem if an employee was active in that date range.
This is perfect for measures, however I also have tables displaying data from the Employee table. Is there anyway that I can have that table filtered as well?
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 |
---|---|
114 | |
99 | |
83 | |
70 | |
60 |
User | Count |
---|---|
150 | |
115 | |
104 | |
89 | |
65 |