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.
Hello,
I have a problem with a report in power bi I try to solve for weeks.
My problem is that I need a list which shows the employees of the company I work for at specific date.
This is an example data set:
And I have Calender Table. But I don´t know if I need the calender table to make my report work or not.
If choose for example 31.12.2017 on the slice, the list in my report should show me people who still working in the company.
The people are: Hans, Alexandra and Mario
30.04.2018: Hans, Alexandra and Mario
I have found some solutions in the forum. Those solutions use measure that shows in new a column "1" if the employee is still working for the company. This way would help me to count the employees.
I hope my explaination is clear enough! 🙂
Does anybody can help me to find a solution for my problem or already have one?
I´m thankfull for every little help!
cfreiburg
Hi @Anonymous,
Believe that your data is not matching you result because on 31/12/2017 you have Hans, Peter, Alexamdre; Michelle and Mario working in the company and on 30/04/2018 the names are Alexandra, Michelle, Mario and Tony on your dataset however create the following measure and add it to your visual as a filter:
Working = VAR Selected_Date = MAX ( 'Calendar'[Date] ) RETURN SWITCH ( TRUE (); MIN ( Employees[Start Date] ) <= Selected_Date && MAX ( Employees[End Date] ) >= Selected_Date; "working"; MIN ( Employees[Start Date] ) <= Selected_Date && MAX ( Employees[End Date] ) = BLANK (); "Working"; "Not working" )
Then set the filter to "Working".
You can also use an interval of dates instead of a specific date.
Regards,
MFelix
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHi @MFelix,
Could you help me, please?
I used your solution to filter a table with employees' start and leave dates. It works perfectly.
But I'd also like the number of employees shown in the table visual to be displayed at the card visual. Applying the same filter to the card doesn't help.
But it leads to an error.
Is there any way to show the number of records on a card after applying a filter?
Thanks in advance!
Hi @Anonymous ,
Try the following measure for the count of the working to put on a card visual:
Measure = COUNTROWS(FILTER(ALL(employee_lifecycle[employee_code]);[Working] = "Working"))
Regards,
MFelix
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHi @MFelix
It really helped a lot! Thank you!
But I am struggling to continue using the filtered table with the data in the related tables
What if I wanted to create a histogram (or any other visual) showing the resulted data but filtered by location for instance? Is it even possible?
Here are the screenshots of the relations and how I imagine the histogram to look like. I tried every possible way I know but I only could get it to work with all the records in the table, without the "Working" measure as a filter
Could you please help? Thanks again!
Please disregard
I made it work by switching from DirectQuery to Import
Thanks anyway!
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 | |
100 | |
75 | |
73 | |
49 |
User | Count |
---|---|
145 | |
109 | |
109 | |
90 | |
64 |