DAX Calculate function, throws error when using calculated measure as filter

[warrencowan]

Hi all, I'm floundering with a calculate function, to sum ad spend, where the ROAS is less than X.

In this case, ad spend is a static column of data for each row,

and ROAS is a calculated measure, created from revenue/spend using the following formula. 

• ROAS = if(iserror(sum('GA-Adwords Table'[Revenue (EUR)])/sum('GA-Adwords Table'[Ad spend (EUR)]))=true,0,sum('GA-Adwords Table'[Revenue (EUR)])/sum('GA-Adwords Table'[Ad spend (EUR)]))

However my calculate function....

• NCS = CALCULATE(sum('GA-Adwords Table'[Ad Spend (EUR)]), [ROAS]<1)

throws the below error message.

• A function 'CALCULATE' has been used in a True/False expression that is used as a table filter expression. This is not allowed.

Can anyone point to what I'm doing wrong.

Any help, critque or abuse for being daft, gratetfully appreciated.

[sibanez]

I have this error:

"RUBRO_C16",IF(VALOR_SALUD_COL_IND[COD RAMO CONT FINAL] IN {"045","034"},CALCULATE(SUM(VALOR_SALUD_COL_IND[VALOR_CALCULADO]),
FILTER(VALOR_SALUD_COL_IND,VALOR_SALUD_COL_IND[COD RUBRO FINAL] = "C16" )),
CALCULATE((SUM(VALOR_SALUD_COL_IND[VALOR_CALCULADO])),
FILTER(VALOR_SALUD_COL_IND,VALOR_SALUD_COL_IND[COD RUBRO FINAL] = "C16" ))),

The True/False expression does not specify a column. Each True/False expressions used as a table filter expression must refer to exactly one column.

[v-sihou-msft · Accepted solution]

@warrencowan

As the error message said, in CALCULATE() function, the second parameter can be a True/False expression. You need to put a filted table context. So your formula should be like:

NCS =
CALCULATE (
    SUM ( 'GA-Adwords Table'[Ad Spend (EUR)] ),
    FILTER ( 'GA-Adwords Table', [ROAS] < 1 )
)

Regards,

[Anonymous]

This solved my problem! thank you kindly!