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

Compete to become Power BI Data Viz World Champion! First round ends August 18th. Get started.

Reply
kylee_anne
Helper II
Helper II

Syntax for Calculate, Sum, Related and AllExcept

Hi,

 

I'm producing schedule portfolio reports.

 

I'm trying to sum given a certain code condition and excepting the project filter on the data which comes from a related table.

 

This works:

PVT = CALCULATE(sum('Resource B'[Value]),'Resource B'[Spreadsheet Field]="Budgeted Units", ALLEXCEPT('Resource B','Resource B'[Key]))
But how do I include RELATED into the ALLEXCEPT?  It will make the report more user friendly if I do.  I want to leave the filter on for Key +Title from Portfolio Project Related Table.  See below for how it is related.
kylee_anne_0-1752479100930.png

Thanks in advance for all helpful responses.

1 ACCEPTED SOLUTION
rohit1991
Super User
Super User

Hi @kylee_anne ,

To sum values in the Resource B table for rows where [Spreadsheet Field] equals "Budgeted Units," but preserve the filter context from a related field ([Key + Title] in the Portfolio List table), you should use ALL to remove all filters from Resource B, and VALUES to keep the filter for [Key + Title] from the related table.


You cannot use RELATED or columns from related tables inside ALLEXCEPT, so this combination achieves your goal: the measure always sums by "Budgeted Units" and only changes when the [Key + Title] from Portfolio List changes in your report.

 

Use this code:

PVT =
CALCULATE(
    SUM('Resource B'[Value]),
    'Resource B'[Spreadsheet Field] = "Budgeted Units",
    ALL('Resource B'),
    VALUES('Portfolio List'[Key + Title])
)

 

ALL('Resource B') removes all filters from the fact table. VALUES('Portfolio List'[Key + Title]) reapplies only the desired filter from your related dimension table, so your visual responds to this context as expected.

 


Did it work? ✔ Give a Kudo • Mark as Solution – help others too!

View solution in original post

4 REPLIES 4
mh2587
Super User
Super User

PVT := // try with treatas might help you
CALCULATE(
    SUM('Resource B'[Value]),
    'Resource B'[Spreadsheet Field] = "Budgeted Units",
    TREATAS(
        VALUES('Portfolio List'[Key + Title]),
        'Resource B'[Key]
    )
)

Did I answer your question? If so, please mark my post as a solution!


Proud to be a Super User!




LinkedIn Icon
Muhammad Hasnain



FBergamaschi
Solution Sage
Solution Sage

This is a DAX question, pls post it in the DAX forum

 

https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/bd-p/DAXCommands

 

Meanwhile, the answer 🙂

 

PVT = 

CALCULATE(

                 sum('Resource B'[Value]),

                 'Resource B'[Spreadsheet Field]="Budgeted Units"

                  ALLEXCEPT('Resource B','Resource B'[Key]), PortfolioList[Title])
)

this assumes there is a one to many relationship between the two tables as I see from the picture

 

You do not need RELATED since the expanded table will make the relates columns active by default

 

If this helped, please consider giving kudos and mark as a solution

@me in replies or I'll lose your thread

consider voting this Power BI idea

Francesco Bergamaschi

MBA, M.Eng, M.Econ, Professor of BI

rohit1991
Super User
Super User

Hi @kylee_anne ,

To sum values in the Resource B table for rows where [Spreadsheet Field] equals "Budgeted Units," but preserve the filter context from a related field ([Key + Title] in the Portfolio List table), you should use ALL to remove all filters from Resource B, and VALUES to keep the filter for [Key + Title] from the related table.


You cannot use RELATED or columns from related tables inside ALLEXCEPT, so this combination achieves your goal: the measure always sums by "Budgeted Units" and only changes when the [Key + Title] from Portfolio List changes in your report.

 

Use this code:

PVT =
CALCULATE(
    SUM('Resource B'[Value]),
    'Resource B'[Spreadsheet Field] = "Budgeted Units",
    ALL('Resource B'),
    VALUES('Portfolio List'[Key + Title])
)

 

ALL('Resource B') removes all filters from the fact table. VALUES('Portfolio List'[Key + Title]) reapplies only the desired filter from your related dimension table, so your visual responds to this context as expected.

 


Did it work? ✔ Give a Kudo • Mark as Solution – help others too!

You do not need a RELATED call into ALLEXCEPT to mention columns in related tables (one to many), but you can avoid removing the filter instead of removing it and then reapplying that with VALUES.

 

The two measures would be different, of course. I only want to clarify that it is not that you cannot use RELATED, you do not need RELATED to write the below code (expanded tables). If you need the syntax with VALUES, OK but that is another point (you are sure with that syntax that a filter is applied to the column even though it was not present at the beginning)

 

Just to make sure on the DAX syntax

 

PVT = 

CALCULATE(

                 sum('Resource B'[Value]),

                 'Resource B'[Spreadsheet Field]="Budgeted Units"

                  ALLEXCEPT('Resource B','Resource B'[Key]), PortfolioList[Title])
)

If this helped, please consider giving kudos and mark as a solution

@me in replies or I'll lose your thread

consider voting this Power BI idea

Francesco Bergamaschi

MBA, M.Eng, M.Econ, Professor of BI

 

Helpful resources

Announcements
August Power BI Update Carousel

Power BI Monthly Update - August 2025

Check out the August 2025 Power BI update to learn about new features.

August 2025 community update carousel

Fabric Community Update - August 2025

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

Top Solution Authors