Running Total Works Referencing Just 1 table but breaks when I add a relationship to another?

[Brotedo]

I'm using a matrix, and want sales values to sum along columns that represent a rolling 12 month view. My matrix has two rows for User and Team that should filter the data. Originally, the table with my sales data didn't have user and team information, it just had a UserId with a relationship to a UserKey table that connected that Id to Name and Team. I tried the following code to make a running total with that setup:

RT Test = 
    Var DateMax = max('Planning Monthly'[DateDiff])
Return
    sumx(
        filter(
            allexcept('Planning Monthly','Planning Monthly'[UserId]), 'Planning Monthly'[DateDiff]<= DateMax),
                [Rolling12Value])

UserId is the column that creates the relationship, but this didn't work. It did create a running total, but it just ignored the row values (gave the same running total for each user/team combo).

I pulled the UserName and Team into my original table using Lookupvalue and modified the code to this and it works:

RT Test = 
    Var DateMax = max('Planning Monthly'[DateDiff])
Return
    sumx(
        filter(
            allexcept('Planning Monthly','Planning Monthly'[UserName],'Planning Monthly'[Team]), 'Planning Monthly'[DateDiff]<= DateMax),
                [Rolling12Value])
                        

 I'd really rather keep using the two tables with the relationship, rather than pulling name and team into my sales table, but the only code I've gotten to work just uses the one table.

[Barthel · Accepted solution]

Hey,

In some cases, especially with matrices, ALLEXCEPT does not work properly. That might be the cause of your problem. Maybe this will work:

RT Test =
VAR DateMax =
    MAX ( 'Planning Monthly'[DateDiff] )
RETURN
    SUMX (
        CALCULATETABLE (
            'Planning Monthly',
            REMOVEFILTERS ( 'Planning Monthly' ),
            VALUES ( 'Planning Monthly'[UserId] ),
            'Planning Monthly'[DateDiff] <= DateMax
        ),
        [Rolling12Value]
    )

Read this article for more info: https://www.sqlbi.com/articles/using-allexcept-versus-all-and-values/ 

[Brotedo]

Thank you so much! This works, and it updates a lot faster than what I had originally used as well!

[tamerj1]

Hi @Barthel 
You are 100% correct. In general using REMOVEFILTERS + VALUES is the best option when creating measures.
Actually @Brotedo is using ALLEXCEPT as a table not as a CALCULATE modifier which is not a good practice