- Forums
- Get Help with Power BI
- Desktop
- Service
- Report Server
- Integrations with Files and Services
- Mobile Apps
- Developer
- DAX Commands and Tips
- Let's Talk Data
- Custom Visuals Development Discussion
- Community Support
- Welcome to the Community
- Community Feedback
- Community Help Blog
- Training and Consulting
- Dashboard in a Day
- EdX Specific Training Discussion Forum

Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- Microsoft Power BI Community
- Forums
- Get Help with Power BI
- Desktop
- CALCULATE has been used in a True/False expression...

Topic Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

mrothschild

Member

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

02-11-2019
11:47 AM

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

)

Solved! Go to Solution.

1 ACCEPTED SOLUTION

Accepted Solutions

mrothschild

Member

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

02-12-2019
05:31 AM

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

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

02-11-2019
12:07 PM

mrothschild

Member

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

02-11-2019
01:00 PM

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

Community Support Team

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

02-12-2019
12:03 AM

Hi @mrothschild,

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

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.

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

mrothschild

Member

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

02-12-2019
05:31 AM

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)

)