Forum Discussion

kylee_anne's avatar
kylee_anne
Helper II
1 year ago
Solved

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: ...
Need Help
  • rohit1991's avatar
    rohit1991
    1 year ago

    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.

     

rohit1991's avatar
rohit1991
Super User
1 year ago

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.

 

  • FBergamaschi's avatar
    FBergamaschi
    Super User
    1 year ago

    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