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 there - i'm quite new to power bi so this might be a simple issue to solve - but your help much appreciated!
I have two tables:
TABLE A (Holding employee records - including leave dates/gender and other employee characteristics)
-----------
Id Leave Date Gender
1 10/01/2018 Male
2 12/01/2018 Male
3 20/01/2018 Female
4 01/02/2018 Female
5 07/02/2018 Female
6 23/02/2018 Female
7 23/02/2018 Male
8 03/03/2018 Female
TABLE B (reach record represents a month of the year)
-----------
Month Start Month End
01/01/2018 31/01/2018
01/02/2018 28/02/2018
01/03/2018 31/03/2018
In table B - I would like to add a new column "Total leavers by month" which would count the total leavers by month. However - what I would also like to achieve is if a user was to then click a report slicer for Male gender only - it would only perform the monthly total count against those male gender employees from Table A.
E.g. when no slicer is selected - table B should be
---
Month Start Month End Total leavers by month
01/01/2018 31/01/2018 3
01/02/2018 28/02/2018 4
01/03/2018 31/03/2018 1
and if Male is selected from a report slicer - it should show:
---
Month Start Month End Total leavers by month
01/01/2018 31/01/2018 2
01/02/2018 28/02/2018 1
01/03/2018 31/03/2018 0
---
There may be any number of characteristics I want to filter in this way on - so it's important it remains flexiable and not hard coded in to DAX formula or in to the report filters (as I need report users to be able to change these during use). Is this possible to achieve in anyway?
Many Thanks
Solved! Go to Solution.
Hi,
You may refer to my solution here.
Hope this helps.
Hi,
You may refer to my solution here.
Hope this helps.
Thanks so much Ashish - i've accepted this as a solution to my question....
However - I've realised i still need help with this and need to provide a bit more detail about what I am trying to achieve.... If you have some time would you be able to see if you can help further with the following:
Thanks
HI @domdom,
You can use only Table A if you wish,
In a visual table, place Leave Date as Date Hierarchy and keep only the year and month, and select Show items with no data to see the month with 0 female or male when users filter the visual.
And Count of Gender as third column.
You'll have something like this and you can filter by gender:
Thanks - however I need to do further calculations on this table such as a rolling 12 month average of the monthly leaver totals - which i'm not sure i'd be able to do without a separate table....
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 |
---|---|
109 | |
98 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
100 | |
86 | |
64 |