CALCULATE has been used in a True/False expression that is used as a table filter expression

[mrothschild]

I'm fairly new to BI and DAX, so I'm a bit confused by programming hierarchy.

Here's a link to my sample BI file: https://www.dropbox.com/s/rbjoqspdjdmtquq/Power%20BI%20Forum%20Sample.pbix?dl=0

The following Measure is throwing the error.  I want to calculate the sum of a column for all but the very last number.  A different column has a count of how many rows are in the column, so I tried to constrain using CALCULATE, but get the error message.

zzMeasure 3 =

CALCULATE(

SUMX(

GROUPBY('_Database Output','_Database Output'[Project],'_Database Output'[Base]),

'_Database Output'[Base]

),

'_Database Output'[Count]<max('_Database Output'[Count])

)

[Anonymous]

Hello @mrothschild

Link does not work for me, could you send a screenshot or picture of your data

[mrothschild]

In the following screenshots, "Alpha" and "Charlie" are roughly the same (though not exactly for reasons not germaine). Except Alpha is comprised of three assets and Charlie is comprised of one.  

The desired outcome is the "Charlie" screenshot below.  

The different forumlas are as follows:

zzIncome with hard number limits =  CALCULATE(  CALCULATE(  SUM('_Bronn Portfolio Analysis Table'[Base]),  '_Bronn Portfolio Analysis Table'[Base]>0,  '_Bronn Portfolio Analysis Table'[Base]<100000  ))

zzzzzHopefully final = 

calculate(  sumx(  GROUPBY('_Bronn Portfolio Analysis Table','_Bronn Portfolio Analysis Table'[Asset ID],'_Bronn Portfolio Analysis Table'[Base]),  '_Bronn Portfolio Analysis Table'[Base]  ),

filter('_Bronn Portfolio Analysis Table','_Bronn Portfolio Analysis Table'[Count]>0),

filter('_Bronn Portfolio Analysis Table','_Bronn Portfolio Analysis Table'[Count]<max('_Bronn Portfolio Analysis Table'[Count]))  )

zzIncome Yield Numerator based on Residual Limit =

CALCULATE(  SUMX(  GROUPBY('_Bronn Portfolio Analysis Table','_Bronn Portfolio Analysis Table'[Asset ID],'_Bronn Portfolio Analysis Table'[Base]),  '_Bronn Portfolio Analysis Table'[Base]),  FILTER('_Bronn Portfolio Analysis Table',  '_Bronn Portfolio Analysis Table'[Count]<max('_Bronn Portfolio Analysis Table'[Count])),  FILTER('_Bronn Portfolio Analysis Table',  '_Bronn Portfolio Analysis Table'[Count]>0)  )

[v-danhe-msft]

Hi @mrothschild,

From your pbix, it seemed your table looks correct with your desired result?

Regards,

Daniel He

[mrothschild · Accepted solution]

It wasn't, but now it is.  Thanks for checking.  

CALCULATE(

    SUM('Table'[Base]),

    ALLSELECTED('Table'[Asset ID]),

Filter('Table','Table'[Count]>0),

FIlter('Table','Table'[Count]<'Table'[Project Term (mos)]+1)

)