Hi Power BI Community,
I've created the calculated column using the formula below to show in the employee table who the manager of the employee. (The employee table is a change log where multiple employees appear more than once for changes in status such as department and change in manager.
Path (name) =
PATH (
'Employee Change'[First Name Last Name (multiple)],
'Employee Change'[Manager name]
)
I've next created a measure to show the path of the employee reporting line.
ConcatenateX reporting line =
CONCATENATEX (
'Employee Change',
IF (
MAX ( 'Calendar'[Date] ) >= 'Employee Change'[Hire date]
&& MAX ( 'Calendar'[Date] ) <= 'Employee Change'[Leaving date],
'Employee Change'[Reporting line] & " ("
& FORMAT ( [Employee tenure], "##0.#" ) & "), ",
BLANK ()
)
)
I'd like to add the tenure in blacket for the all the employees appearing in the reporting path, but it only shows the reporting line for the person of the row context in the employee change log table.
For example the output looks like this, instead of
Name of highest hierarchy ← Name of 2nd hierarchy ← Name of 3nd hierarchy (tenure year)
instead of showing results like below:
Name of highest hierarchy (tenure year) ← Name of 2nd hierarchy (tenure year) ← Name of 3nd hierarchy (tenure year)
I think this is due to the limitation of using the calculation column and the constrains of path respecting row context. Please could you let me know if we can use path function in a more flexible way so that I can add tenure years to all the names appearing in the reporting path?
Thank you for your help.
