SUM or CALCULATE Measure that is not affected by selected a Project code from a project list

[Deevo_]

Hi all,

I am hoping there is someone out there who can figure out the missing DAX measure i need.

I have a really simple scenario.

Context:

• I am creating a report that somewhat is like a calculator.
• All on one page there are 3 tables.
  • Table 1 is a list of project codes, i click on one project code and then it filters the values in table 2 and 3.
  • Table 2, I can see which roles are required for the selected project and the Estimated FTE required to complete the project.
  • Table 3, I can see the same roles on table 2, but I need this to calculate the Total Assigned FTE for each role without being affected by the selected project code.

What I currently have:

• 4 tables: Projects, Assignments, ResourceData, Roles
  • Each of these 4 are linked together by unique ID's
  • Measures:
    • Projects:
      • Total Estimated FTE Required = SUM('Projects'[Estimated_FTE_Required])
    • Assignments:
      • Total Assigned FTE = SUM('Assignments'[Assigned_Effort_FTE])
    • Resource Data:
      • Total Resource Supply FTE = SUM('ResourceData'[ResourceSupply (FTE)])
        
    • Other measures:
      • Available Supply FTE = [Total Resource Supply FTE] - [Total Assigned FTE]
      • Unmet Estimated FTE Required = [Total Estimated FTE Required] - [Total Assigned FTE]

What is currently happening:

• When i select a project code, it changes all the values for all the [Total Assigned FTE] measure in table 2 and 3.

Requirements:

• I need a measure that will calculate Total Assigned FTE = SUM('Assignments'[Assigned_Effort_FTE]) without being affected by the selected project code

What i have tried:

Total Assigned FTE (No FILTER) = CALCULATE([Total Assigned FTE], ALL('Projects'))

• This appears to work when nothing is selected, but as soon as i select a Project code form the list, it changes. I need it to stay static.

It sounds simple, but I cannot get it to work.

Many thanks in advance

[mlsx4 · Accepted solution]

Are you using slicers or filters from the pane? I'm asking because of the behaviour of Auto-exist in DAX. If you are using filters from the pane, there's no way to escape from them. 

[Deevo_]

Hi mlsx4, yes good question. I am using both page filters and also the selected values on the report page itself (the values within the tables).

I appreciate your time and input. 

I will have to test another method which requires referencing a table and creating a measure which uses the values in the referenced table. So it should be disconnected from everything and no filters will affect it whatsoever. Hope it works!