DAX Joint value

0
votes

I am trying to create a measure in DAX to find the joint value (sum) of a fact table by filtering two dimension tables to have the same column value.

Here's an image of the relationship

enter image description here

Each Department in Users are linked to table Registret. They are giving resources to projects. The table Projects are Taking Resourcs. What I am trying to accomplish is a measure that can find the amount a department uses of it's own resources. So I figure if I can filter Registret[Users] and Registre[Project] where Users[Department] = Projects[Department] I would get the value.

I'd like to use the Projects[Department] as a basis. So that table below would show the amount of internal department registration by each Department in Projects.

Department(Projects)   InternalRegistration
   A                           Value 
   B                           Value

So far I've been trying with something like 

CALCULATE(Registret[Registret]; FILTER(Users; Users[Department] IN ALLSELECTED(Projects[Departments])))

But this will only show the correct value if I in a slicer single out a Department from table Projects. Is it possible the solution is some Joint table between Users and Projects?

Edit: enter image description here

The top table shows the matrix of joint values in Registret between Users and Projects.

Edit2:

enter image description here Image of my table data.

1
powerbidax

1 Answers

1
votes

Notice that the filter arrows only point downward. This means that selecting Projects[Departments] isn't going to filter Users[Departments] since there is no filter path connecting them. You are also missing the SUM function in your measure.

Try tweaking your measure to the following:

= CALCULATE(SUM(Registret[Registret]);
      FILTER(Users; Users[Department] = SELECTEDVALUE(Projects[Departments])))

If you're working in a matrix or a table, the SELECTEDVALUE should pick up the row/column filter context from the visual, whereas ALLSELECTED only picks up filter context from slicers or report/page/visual level filters.

You can also use ...IN VALUES(... instead of ...= SELECTEDVALUE(....

= CALCULATE(SUM(Registret[Registret]),
      FILTER(Users, Users[Department] IN VALUES(Projects[Department])))

Both of these should produce the following:

Matrix