Forum Discussion

astroadmin's avatar
astroadmin
New Member
6 years ago
Solved

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[E...
Need Help
Show and Tell
  • Greg_Deckler's avatar
    Greg_Deckler
    6 years ago

    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's avatar
astroadmin
New Member
6 years ago

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's avatar
amitchandak
Super User
6 years ago

The first one is suggested as column not Measure

  • astroadmin's avatar
    astroadmin
    New Member
    6 years ago

    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.