Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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.
Solved! Go to 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/
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!
User | Count |
---|---|
41 | |
27 | |
23 | |
19 | |
16 |
User | Count |
---|---|
55 | |
35 | |
21 | |
18 | |
15 |