Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
Hi
I've spent a bit of time looking through the forum to try and find a solution that helps solve my problem to no avail!
Essentially I have a Role table:
Role ID |
1001 |
1002 |
... |
And I want to be able to add the current employee or vacancy.
Staff ID | Role ID | Start Date | End Date |
1001 | ... | ... | ... |
1002 | ... | ... | ... |
Vacancy ID | Role ID | Start Date | End Date |
1001 | ... | ... | ... |
1002 | ... | ... | ... |
My current thinking is to add a column onto the Role table looking up the last entry from each table. A slight complication is that some roles are pre filled in the future so would have to be up to the current date.
I've spent a while trying to get this working and any guidance as to whether this is a sensible approach or where/how to solve it would be very much appreciated!
Thanks in advance!
Solved! Go to Solution.
Hi, @metcala
Please try formula like:
Vacancy ID =
VAR _MAXDATE=CALCULATE(MAX('Table 2'[Start Date]),ALLEXCEPT('Table 2','Table 2'[Role ID]))
RETURN CALCULATE(MAX('Table 2'[Vacancy ID]),FILTER(ALL('Table 2'),'Table 2'[Start Date]=_MAXDATE&&'Table 2'[Role ID]=MAX('Role table'[Role ID])))
Staff ID =
VAR _MAXDATE=CALCULATE(MAX('Table 1'[Start Date]),ALLEXCEPT('Table 1','Table 1'[Role ID]))
RETURN CALCULATE(MAX('Table 1'[Staff ID]),FILTER(ALL('Table 1'),'Table 1'[Start Date]=_MAXDATE&&'Table 1'[Role ID]=MAX('Role table'[Role ID])))
If it doesn‘t work, please share a sample file and expected outcome in excel for further research.
Best Regards,
Community Support Team _ Eason
Thanks for your replies and apologies if my explanation wasn't clear!
I am looking to add a column showing either the current Staff ID or Vacancy ID. So for the example below Role 101 should show V002.
Staff ID | Role ID | Start Date | End Date |
S001 | 101 | 1/8/2020 | 31/10/2022 |
Vacancy ID | Role ID | Start Date | End Date |
V001 | 101 | 1/1/2020 | 31/7/2020 |
V002 | 101 | 1/11/2022 |
Hi, @metcala
Please try formula like:
Vacancy ID =
VAR _MAXDATE=CALCULATE(MAX('Table 2'[Start Date]),ALLEXCEPT('Table 2','Table 2'[Role ID]))
RETURN CALCULATE(MAX('Table 2'[Vacancy ID]),FILTER(ALL('Table 2'),'Table 2'[Start Date]=_MAXDATE&&'Table 2'[Role ID]=MAX('Role table'[Role ID])))
Staff ID =
VAR _MAXDATE=CALCULATE(MAX('Table 1'[Start Date]),ALLEXCEPT('Table 1','Table 1'[Role ID]))
RETURN CALCULATE(MAX('Table 1'[Staff ID]),FILTER(ALL('Table 1'),'Table 1'[Start Date]=_MAXDATE&&'Table 1'[Role ID]=MAX('Role table'[Role ID])))
If it doesn‘t work, please share a sample file and expected outcome in excel for further research.
Best Regards,
Community Support Team _ Eason
Sorry, but this explanation is unclear.
@metcala , Can you share the expected output
These are ways to deal with range
Measure way
Power BI Dax Measure- Allocate data between Range: https://youtu.be/O653vwLTUzM
https://community.powerbi.com/t5/Community-Blog/How-to-divide-distribute-values-between-start-date-o...
Tables way
https://amitchandak.medium.com/dax-get-all-dates-between-the-start-and-end-date-8f3dac4ff90b
https://amitchandak.medium.com/power-query-get-all-dates-between-the-start-and-end-date-9ad6a84cf5f2
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
33 | |
16 | |
13 | |
10 | |
8 |
User | Count |
---|---|
59 | |
20 | |
12 | |
11 | |
10 |