Hi,
I am trying to calculate a measure based on dynamic conditions set by another table.
Some background:
Activity Table (Activity x Type x Funding): This table contains activity list which contains the conditions I want to use as filters when calculating a measure from another table. Some activity might only have one condition. Some might have two conditions.
Student Table (Organisation x Type x Funding x Load): This contains the student data by organisation, type, funding and load.
Unassigned Activity (Organisation x Activity): This contains all the unassigned activity by organisation
In summary, I would like to know how much the student load for the unassigned activity based on filter conditions set by the activity table.
An example should make this clearer:
Say in the Unassigned table, I have a Teaching Activity for Dept A. I would like to know the number of student load for that activity based on the activity table. The activity table sets that Teaching activity can only be applied to type="UG" and Funding="ANY" (meaning all field values are valid). So I would like to return the load no from Student Table that contains type="UG" and whatever value there is in funding.
For Research, the Activity table sets that it can only be applied to type="HDR" and funding="RTS". So I would like to return the load from Student table that contains type="HDR" and funding="HDR".
Can anyone offer some advice please? I have tried some variations of CALCULATE and FILTER to no avail.
I have attached the file for more details.
Thank you
I am trying to calculate a measure based on dynamic conditions set by another table.
Some background:
Activity Table (Activity x Type x Funding): This table contains activity list which contains the conditions I want to use as filters when calculating a measure from another table. Some activity might only have one condition. Some might have two conditions.
Student Table (Organisation x Type x Funding x Load): This contains the student data by organisation, type, funding and load.
Unassigned Activity (Organisation x Activity): This contains all the unassigned activity by organisation
In summary, I would like to know how much the student load for the unassigned activity based on filter conditions set by the activity table.
An example should make this clearer:
Say in the Unassigned table, I have a Teaching Activity for Dept A. I would like to know the number of student load for that activity based on the activity table. The activity table sets that Teaching activity can only be applied to type="UG" and Funding="ANY" (meaning all field values are valid). So I would like to return the load no from Student Table that contains type="UG" and whatever value there is in funding.
For Research, the Activity table sets that it can only be applied to type="HDR" and funding="RTS". So I would like to return the load from Student table that contains type="HDR" and funding="HDR".
Can anyone offer some advice please? I have tried some variations of CALCULATE and FILTER to no avail.
I have attached the file for more details.
Thank you