topic Re: Running Total Works Referencing Just 1 table but breaks when I add a relationship to another? in DAX Commands and Tips

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

Brotedo — Fri, 29 Jul 2022 18:18:29 GMT

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.

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

Barthel — Sat, 30 Jul 2022 13:08:50 GMT

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/

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

tamerj1 — Sat, 30 Jul 2022 15:10:45 GMT

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

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

Brotedo — Mon, 01 Aug 2022 12:46:29 GMT

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