DAX - Calculate with Filter using table column value

[astroadmin]

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. 

[Greg_Deckler · Accepted solution]

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
     )
)

[astroadmin]

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.

[amitchandak]

The first one is suggested as column not Measure

[astroadmin]

Hi @amitchandak and @Greg_Deckler ,

I see what you mean, yes the newly added field was a measure, thats why it was not working. When I changed new measure to new column, things started to work!

Thanks a lot.