Need help with calculated measure

Hello,

We have insight registry where are employees and courses as objects.

Employees have attributes: Name, Unit (linked to single Unit object), Branch (linked to single Branch object)

Courses have attributes: Name, Obligatory for (linked to 1 or multiple Unit object(s)), Branch (linked to 1 or multiple Branch object(s))

We need to make eazybi report which shows Employees that need to take a specific course. I can’t manage to make such calculated measure where it displays Employee names if they belong to Unit AND Branch which are marked on the Course. Will add also pictures (with randomly generated data) which show what we are trying to achieve.
Screenshot 2022-01-05 at 12.41.21

Maybe somebody can point me to right direction. Thanks!

Hi,

This is quite a tricky use case. Writing the formula may get stuck since we need to operate with the Object dimension in two ways (for Courses and Employees). For that, we need the help of additional Object hierarchies (which is a new feature in version 6.4 of eazyBI) to implement the filter conditions properly.

I created the following schema according to your description:

I selected the import of all linked dimensions:

After the import, I need to create additional object hierarchies for grouping them by Units and Branches:

After I do that, my Object dimension looks the following:

Even with these hierarchies, the formula is quite complex. The idea is to use one of the new hierarchies (e.g., Employee Branch) to generate the full list of employees and check if the employee is a member of Branch AND Unit with the mandatory course. It is assumed that Courses are objects in the report rows:

Generate(
  Filter([Object.Employee Branch].[Object].Members,
  [Object.Employee Branch].CurrentHierarchyMember.Parent.Name<>"(none)"
  AND
  Instr([Measures].[Object Course Obligatory for Branch],
  [Object.Employee Branch].Parent.Name)>0
  AND
  Instr(
    [Measures].[Object Course Obligatory for Unit],
    [Object.Employee Unit].[Object].GetMemberByKey(
      [Object.Employee Branch].CurrentMember.Key
    ).Parent.Name
  )>0
  )
  ,
  [Object.Employee Branch].CurrentHierarchyMember.Name,
  CHR(10)
)

Thank you for providing the test case, this formula worked as expected for the data provided:

Kindly,
Janis, eazyBI support

Thank you, Janis!

This was exactly what was needed. It works nicely for us.

Br,

1 Like