Forum Discussion
Creating a new row with IF query in DAX
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.
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 |
Community ChampionWhat happened to the sick leave data?
I've just given an example set of data. Once I know how to treat two tables I'm sure I can work out how to treat three.
- HotChilli
You can link the tables on Employee Number in Relationship View.
Create a Measure to ensure the employees with no leave still show up
Emp leave = SUM(EmployeeLeave[Annual Leave]) + 0
Then pull the relevant fields on to a table visualisation
Thanks but that's not what I'm looking for. I need to create a date-based table that logs all activity against the individual. The key is to have every staff member represented against every month they are active (time beween employee start date and employee end date).