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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Brotedo
Helper I
Helper I

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

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.

1 ACCEPTED SOLUTION
Barthel
Solution Sage
Solution Sage

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/ 

View solution in original post

3 REPLIES 3
Barthel
Solution Sage
Solution Sage

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/ 

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

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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

Top Solution Authors