Forum Discussion
Creating a new row with IF query in DAX
Hello,
I'm working with HR data and need to create a row to represent staff activity undertaken each by month. For example:
| Month | Name | Sick Leave in Month | Annual Leave in Month |
| April | Joe | 1 | 0 |
| May | Joe | 0 | 2 |
| June | Joe | 1 | 1 |
| May | Paul | 2 | 0 |
| June | Paul | 1 | 1 |
| June | Jon | 0 | 0 |
I have a list of all staff, their start date and end date, and all the activity data with relevant dates. One of the solutions I thought might work, but doesn't, is to create an IF query along the lines of:
In Month = IF(AND([Employee Start Date]<DATE(2019,04,01), [Leavers Date]>DATE(2019,04,30)), "April 2018", IF(AND([Employee Start Date]<DATE(2019,05,01), [Leavers Date]>DATE(2019,05,31)), "May 2018", IF(AND([Employee Start Date]<DATE(2019,06,01), [Leavers Date]>DATE(2019,06,30)), "June 2018", IF(AND([Employee Start Date]<DATE(2019,07,01), [Leavers Date]>DATE(2019,07,31)), "July 2018", IF(AND([Employee Start Date]<DATE(2019,08,01), [Leavers Date]>DATE(2019,08,31)), "August 2018", IF(AND([Employee Start Date]<DATE(2019,09,01), [Leavers Date]>DATE(2019,08,30)), "September 2018", BLANK()))))))
However this only attribites the first relevant result (eg Joe would be April 2018, Paul would be May 2018 and Joe would by June 2018). Can anyone think of a solution? Essentially I want to write a code that creates a new line and duplicates all other information (eg name).
Hi Anonymous
1. In Query Editor go to Transform > New Query > New Source > Blank Query,
2. Go to advanced editor and paste the code and rename the Query1 to ExpandMonths.
3. When in your staff data table go to Add Column > General > Invoke Custom Function.
4. Make sure you set everything as on the screenshot below.5. this will add new column "ExpandMonths", all you need to do now is click the two arrows an select expand to new rows.
Best Regards,
Mariusz
If this post helps, then please consider Accepting it as the solution.
Please feel free to connect with me.
12 Replies
- HotChilli
Community Champion
Can you post some sample data please and someone will help you out?
I have two sets of data, the staff data:
Employee Full Name Employee Number Employee Start Date Employee End Date Bloggs, Joe 11111 01/04/2018 01/01/4721 Bloggs, Janet 22222 15/01/2018 15/04/2018 Bloggs, Jane 33333 30/08/2015 30/02/2019 Bloggs, Jim 44444 05/02/2017 01/01/4721 And the annual leave data, NB this only reports if someone has taken annual leave, it does not include all staff:
Employee Full Name Employee Number Annual Leave Month Bloggs, Joe 11111 1 Jun-19 Bloggs, Janet 22222 1.5 Jun-19 Bloggs, Jim 44444 6 Jun-19 Bloggs, Janet 22222 6 Jul-19 Bloggs, Jane 33333 5 Jul-19 Bloggs, Jim 44444 14 Jul-19 I need to create a table like this:
Active Month Employee Number Employee Start Date Employee End Date Annual Leave Jun-19 11111 01/04/2018 01/01/4721 1 Jun-19 22222 15/01/2018 15/04/2018 1.5 Jun-19 33333 30/08/2015 30/02/2019 0 Jun-19 44444 05/02/2017 01/01/4721 6 Jul-19 11111 01/04/2018 01/01/4721 0 Jul-19 22222 15/01/2018 15/04/2018 6 Jul-19 33333 30/08/2015 30/02/2019 5 Jul-19 44444 05/02/2017 01/01/4721 14 - HotChilli
What happened to the sick leave data?
