Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago

Can't Display the Visual Error, Multiple Filters and Relationships

Hi

 

I am attempting to make it possible to filter/splice my calculation but I am recieving an error due to "ambigous path".

For information, I have the following tables: "Employee Register", "Incidents" and "Hours Worked" as well as a "Date" Table.

 

Employee Register Table

Employee NumberDepartmentBusiness UnitLocationEmployment TypeeStart DateEnd Date

Incidents Table

Incidnet NumberDate of IncidentDepartmentBusiness UnitLocationIncident TypeInjury TypeEmployment Type

Hours Worked Table

Employee NumberPosted DateQuantity (Hours)Rate

 

Active Relationships

  • Employee Register (Start Date) and Date (Date), Many to one, Single
  • Hours Worked (Employee Number) and Employee Register (Employee Number), Many to one, Single
  • Incidents (Date of Incident) and Date (Date), Many to one, Single

Inactive Relationships

  • Employee Register (End Date) and Date Table (Date), Many to one, Single
  • Hours Worked (Posted Date) and Date (Date)
  • Incidents (Department) and Employee Register (Department), Many to Many, Both
  • Incidents (Business Unit) and Employee Register (Buisness Unit), Many to Many, Both
  • Incidents (Location) and Employee Register (Location), Many to Many, Both
  • Incidents (Employee Type) and Employee Register (Employee Type), Many to Many, Both

I've currently got the following measurement to calculate the rolling 12 month LTI numbers:

 

LTI 12MR =

VAR _End = MAX ('Date'[Date])
VAR _Start = EOMONTH ( _End, -12)+1

RETURN
(
    CALCULATE(
        COUNT(Incidents[Injury Type]),
        Incidents[Injury Type]="LTI",
        USERELATIONSHIP(Incidents[Date of Incident],'Date'[Date]),
        USERELATIONSHIP(Employee Register[Department],Incidents[Department]),
        DATESBETWEEN('Date'[Date],_Start,_End)
            )
)
 
The calculation of course, only allows me to splice/filter for Departments; however, I also want to be able to filter for Business Units, Employee Type and/or Location. I did tried to add additional use relationships agurments (eg. USERELATIONSHIP(Employee Register[Business Unit],Incidents[Business Unit])", but it is giving me the error. How can I achieve this? Should I be doing Crossfilters? Any advice will be great.
 
Thank you in advance

1 Reply