Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
mrothschild
Continued Contributor
Continued Contributor

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

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

View solution in original post

4 REPLIES 4
Anonymous
Not applicable

Hello @mrothschild

 

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

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

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.

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

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.