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 guys,
I am trying to solve this for a while and couldn't get a solution. I work in recruitment and I have to make daily/weekly/monthly reports on the recruiter's activity. I have 45 columns in Power Bi full of date/time that describe an action that was made by a recruiter, for example, if a candidate passes from Test to Interview, in the Interview column will appear the date and time when this change was made.
This is a simplified sample of what I got, I put colors for each month to be more clear.
And this is how 2 of the columns look in reality.
Can I make a slicer that would filter every action that was made between 2 certain dates, in all the 45 columns?
For example, in the simplified version, I want to see how many actions have been made in January (gray), or February(green) in each column, by each recruiter?
In my real report, it should display something like this, here I used as reference one column of dates like "Candidate Submit Date", but it doesn't give me a real-time activity of recruiters as if a candidate submitted a month before I set up a date in a slicer, the activity for that specific candidate will not appear, even though he passed through some processes between the dates I choose and it's a faulty report.
The reason I insist on the slicer method and not a formula is that I want all the recruiters and recruitment managers to be able to use the slicer themselves and export the data they need without my assistance.
If you have any ideas or need further information, please let me know.
Thanks a lot for your assistance!
Solved! Go to Solution.
I think you need to transform your data in Power Query first.
Here is a sample of your data (I just keyed some stuff in)
I then selected all of the columns except the recruiter, and on the Transform tab, select Unpivot Column. Then I renamed the columns to make more sense and got this:
Now, import that into Power BI, and link the date column to a date table in Power BI (See this article if you need help with a date table).
Then put the date, or month, or quarter, or whatever, from the date table into your slicer, and the functions (screening, test, etc) and the recruiter names in your visuals, and the date slicer will cause your other visuals to show all related items about that time period. So if Month is in the slicer, all January items would show up in the visual if you selected January in the slicer.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingI think you need to transform your data in Power Query first.
Here is a sample of your data (I just keyed some stuff in)
I then selected all of the columns except the recruiter, and on the Transform tab, select Unpivot Column. Then I renamed the columns to make more sense and got this:
Now, import that into Power BI, and link the date column to a date table in Power BI (See this article if you need help with a date table).
Then put the date, or month, or quarter, or whatever, from the date table into your slicer, and the functions (screening, test, etc) and the recruiter names in your visuals, and the date slicer will cause your other visuals to show all related items about that time period. So if Month is in the slicer, all January items would show up in the visual if you selected January in the slicer.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingThanks a lot, edhans!
Your solution is perfect for what I wanted, I used the date table as a slicer to filter the dates I need, and I made both a table and a matrix table to show the results how I need them to be! That's amazing!
@OvidiuNeacsu @edhans I'm having a similar issue. I followed your solution, but am having an issue with incorrect totals for "count of date" columns as detailed here: https://community.powerbi.com/t5/Desktop/Filtering-Multiple-quot-Count-of-Date-quot-Columns-with-One...
Any help would be appreciated!
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 |
---|---|
113 | |
100 | |
78 | |
76 | |
52 |
User | Count |
---|---|
146 | |
109 | |
106 | |
88 | |
61 |