Forum Discussion
DAX - Calculate with Filter using table column value
Hi Everyone,
I am trying to make a DAX function work. For some reason, searching for hours couldnt find a solution. Any help will be greatly appriciated.
Result =
CALCULATE(
SUM(DATA[Estimated Annual Revenue]),
FILTER (
ALL ( DATA[BusinessUnit] ), DATA[BusinessUnit] = This Rows BU column value
)
)
So when I type something static the DAX function works. However I want to be able to filter the DATA table based on the current tables BU column value.
If this is a column, you should be able to use:
Result = CALCULATE( SUM(DATA[Estimated Annual Revenue]), FILTER ( ALL ( DATA[BusinessUnit] ), DATA[BusinessUnit] = EARLIER([BU]) ) )If it is a measure, you could use:
Result = VAR __BU = MAX('DATA'[BU]) RETURN CALCULATE( SUM(DATA[Estimated Annual Revenue]), FILTER ( ALL ( DATA[BusinessUnit] ), DATA[BusinessUnit] = __BU ) )
4 Replies
If this is a column, you should be able to use:
Result = CALCULATE( SUM(DATA[Estimated Annual Revenue]), FILTER ( ALL ( DATA[BusinessUnit] ), DATA[BusinessUnit] = EARLIER([BU]) ) )If it is a measure, you could use:
Result = VAR __BU = MAX('DATA'[BU]) RETURN CALCULATE( SUM(DATA[Estimated Annual Revenue]), FILTER ( ALL ( DATA[BusinessUnit] ), DATA[BusinessUnit] = __BU ) )Hi Greg,
My question may not have been clear enough.
So there are two tables DATA and Dashboard_1, I am adding a measure to Dashboard_1 to filter and sum values in DATA. Both tables have a column named BU which has to match.
To sum up, the result should return Summation of DATA[Estimated Annual Revenue] for those records Dashboard_1[BU] = DATA[BU]
When I try your solution I receive an error;
EARLIER/EARLIEST refers to an earlier row context which doesn't exist.
The first one is suggested as column not Measure
