Calculated column based on values in other related tables

[Water]

Experts,

Please help me with the code to include the following with what I believe should be a calculated column.

QUESTION: How would I add a calculated column (named: TYPE_OF_JOB)  in the “WO WITH JOB DETAIL”-table so that each job for each specific unit is evaluated as either:

1. A job for that unit that appears in the “Standard Job MCC”-table as a prescribed maintenance job assigned to that unit as part of its MCC group of jobs = “MCC JOB”
2. A job that is assigned to that specific unit as an extra prescribed maintenance job not part of the MCC list, but an extra maintenance job assigned via the “Standard Job Unit”-table = “UNIT JOB”
3. Everything else as a “NON_MAINTENACE JOB”.

Background:

We have a list of units (vehicles).

Each unit has a unique UNIT_NUMBER (with slight variance in spelling between tables, e.g. UNITNUMBER or UNIT_NO).

Each vehicle is assigned a MCC (Maintenance Code), which groups the number of prescribed maintenance jobs that must be performed on that specific unit. Each unit in the “Unit List”-table has a MCC field and number, which goes through the “MCC masterlist”-table to the “Standard Job MCC”-table where one can find all the jobs associated with a specific MCC as assigned to a unit. (Different units can have the same MCC.)

Sometimes an additional maintenance job is assigned to a specific unit which is not part of the assigned MCC group of jobs. These jobs are captured in the “Standard Job Unit”-table. Through the UNIT_NUMBER fields one can see which additional maintenance jobs are assigned to a specific unit.

Then, on each unit many different jobs are performed during a unit’s lifetime, sometimes prescribed maintenance jobs, other times non-maintenance jobs.

To perform one or more jobs on a unit at any specific time, a Work Order (WO) is opened, and all the jobs performed at that moment fall under the same WO. The “WO TOP LEVEL”-table contains a list of all the WO performed and linked to the “Unit list”-table via the UNIT_NUMBER field. To see which jobs were performed as part of a WO, look at the breakdown of each WO to job level in the “WO WITH JOB DETAIL”-table.

One can filter on all the jobs performed on a unit via the relationship from  “WO WITH JOB DETAIL” up to “WO TOP LEVEL” up to the “Unit List”-table, OR filtering on the UNIT_NOs in the “WO WITH JOB DETAIL”-table.

See relationships below.

QUESTION (again) : How would I add a calculated column (named: TYPE_OF_JOB)  in the “WO WITH JOB DETAIL”-table so that each job for each specific unit is evaluated as either:

1. A job for that unit that appears in the “Standard Job MCC”-table as a prescribed maintenance job assigned to that unit as part of its MCC group of jobs = “MCC JOB”
2. A job that is assigned to that specific unit as an extra prescribed maintenance job not part of the MCC list, but an extra maintenance job assigned via the “Standard Job Unit”-table = “UNIT JOB”
3. Everything else as a “NON_MAINTENACE JOB”.

See sample data here on the various tabs named after each applicable table: Excel sample data 

Sample data already loaded in Sample Power BI dashboard: Power BI dashboard set up

Thank you!

Water

[lbendlin]

One can filter on all the jobs performed on a unit via the relationship from  “WO WITH JOB DETAIL” up to “WO TOP LEVEL” up to the “Unit List”-table, OR filtering on the UNIT_NOs in the “WO WITH JOB DETAIL”-table.

Filtering up from the fact to the dimension is not recommended. It messes with your data model.  Instead, hide the UNIT_NO field in the Job Detail table.

This should be your data model:

None of your reference tables are visible from the Job Detail table so you need to resort to lookups.

TYPE_OF_JOB = 
SWITCH(TRUE(),
CALCULATE(countrows('Standard Job MCC'),TREATAS({[JOB]},'Standard Job MCC'[JOB]))>0,"MCC JOB",
CALCULATE(countrows('Standard Job UNit'),TREATAS({[JOB]},'Standard Job UNit'[JOB]))>0,"UNIT JOB",
"NON_MAINTENACE_JOB")

See attached.