Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Don't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.

Reply
Deevo_
Resolver I
Resolver I

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

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

1 ACCEPTED SOLUTION
mlsx4
Memorable Member
Memorable Member

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. 

View solution in original post

2 REPLIES 2
mlsx4
Memorable Member
Memorable Member

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. 

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!

 

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.

Jan25PBI_Carousel

Power BI Monthly Update - January 2025

Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.

Jan NL Carousel

Fabric Community Update - January 2025

Find out what's new and trending in the Fabric community.