DAX Difference between columns in a one-to-many relationship

Question

I'am struggling with a DAX measure (or calculated column maybe?) for getting the difference between 2 columns in a one-to-many relationship.

Here's an example

A table contains required activities divided in tasks (task 1 and task 2).

Another table is fullfilled with employees who work part of their time on the different tasks.

I am asked to provide the difference between the task required times (in required_activities table) and actual times (in employee table)

data model:

tables:

So I would like to get a Pivot table like this one:

How to build the measure for calculating the difference (employee real times - required_activities times)? I tried using RELATED function but can't make it work.

Answer 1

There are two methods, Method 1 will get you going faster but is limited - whereas Method 2 needs a little effort to shape your data.
Method 1 answers your question directly.
Method 2 is the recommended approach.

---

Method 1

If there are a smallish, finite number of tasks then try the following:
Add a new table to your data model. Let's call it task that looks like:

Task
task 1
task 2
task 3

Single column table with a row for each of your task's name. No relationship needed.

Next, add these three measures to your model.

required:=
  var task1 = SUM(required_activites[task 1])
  var task2 = SUM(required_activites[task 2])
  var task3 = SUM(required_activites[task 3])
  RETURN SWITCH( SELECTEDVALUE('task'[Task]),
    "task 1", task1,
    "task 2", task2,
    "task 3", task3,
    task1 + task2 + task3
  )

real:=
  var task1 = SUM(employees[task 1])
  var task2 = SUM(employees[task 2])
  var task3 = SUM(employees[task 3])
  RETURN SWITCH( SELECTEDVALUE('task'[Task]),
    "task 1", task1,
    "task 2", task2,
    "task 3", task3,
    task1 + task2 + task3
  )

diff:= [real] - [required]

Updated

Note: If your version of Excel doesn't support SELECTEDVALUE then replace it with:

   RETURN SWITCH( IF( HASONEVALUE('task'[Task]), VALUES('task'[Task]) ) , 

And that should do it. Ensure you are adding 'task'[Task] in your Pivot table Rows.

---

Method 2

The other method requires altering the shape of your data model and you will need to use Power Query to do this. This method is the recommended approach as it will scale and perform optimally. (Lookup 'Star schema')

For both of your tables, select all the task columns and unpivot them so you get a structure like this:

activity id	Task	Value
a	task 1	0.5
a	task 2	0.5
b	task 1	0.2
b	task 2	0.3

Updated

While in PowerQuery, select your required_activities query/table (the one that you've unpivoted), right-click and Duplicate. Rename this new query to Dim Activity.

• Right-click on the activity id column header, and select Remove Other Columns.
• And one more time, right-click on the column header and select Remove Duplicates

Again, select your required_activities query/table, right-click and Duplicate. Rename this new query to Dim Task. Follow the same steps so that you are left with a Task column without duplicates. (Note, this step removes the need for the earlier task table mentioned in Method 1.

In your Data Model (Power Pivot), create relationships like so:

Once you have that, the DAX measures become super simple:

required:= SUM(required_activites[Value])

real:=SUM(employees[Value])

diff:= [real] - [required]

Updated

In your Pivots and Charts, use Dim Activity & Dim Task for your rows/columns etc...