cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
mrothschild Member
Member

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

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

)
 
 
1 ACCEPTED SOLUTION

Accepted Solutions
mrothschild Member
Member

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

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)
)
4 REPLIES 4
Collin Member
Member

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

Hello @mrothschild

 

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

mrothschild Member
Member

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

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

 

 

Screenshot - Alpha.PNGScreenshot - Charlie.PNG

Community Support Team
Community Support Team

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

Hi @mrothschild,

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

Capture.PNG

Regards,

Daniel He

Community Support Team _ Daniel He
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
mrothschild Member
Member

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

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