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

Get inspired! Check out the entries from the Power BI DataViz World Championships preliminary rounds and give kudos to your favorites. View the vizzies.

Reply
Tini-Bee
Regular Visitor

Unable to apply measure to CALCULATE statement

I want to utilise the CALCULATE MAX and ALL('Date') as follows:

Sum Range Space =

varTest = CALCULATE(MAX([SumRangeSpace1], ALL('Date')))
VAR RangeSpaceCount = CALCULATE(COUNT(' Build_Capacity'[RangeSpace]

 

VAR TotalCount =  CALCULATE(DISTINCTCOUNT('Build_Usage'[ID]), ALL('Date'))

VAR BLANKK = BLANK()

RETURN

    IF (

        (

            ISFILTERED ( 'Hs'[House Name] )

                && RangeSpaceCount = TotalCount

        ),

        [SumRangeSpace1],

        BLANK()

    )

The underlying measure [SumRangeSpace1] is

SumRangeSpace1 = CALCULATE (

    SUM( 'Build_Capacity'[RangeSpace] ),

    TREATAS (

        VALUES ( 'Build_Usage'[ID] ),

        'Build_Capacity'[ID]

    )

)

1 ACCEPTED SOLUTION
Tini-Bee
Regular Visitor

rectified this issue using 
CALCULATE([SumRangeSpace1], ALL('Date'))

View solution in original post

4 REPLIES 4
Tini-Bee
Regular Visitor

rectified this issue using 
CALCULATE([SumRangeSpace1], ALL('Date'))

Tini-Bee
Regular Visitor

@bhanu_gautam 
VAR varTest = CALCULATE(MAX([SumRangeSpace1]), ALL('Date'))  doesn't work

 

error returned is 'The expression refers to multiple columns'

SumRangeSpace =
VAR varTest = CALCULATE(MAX([SumRangeSpace1]), ALL('Date'))
VAR RangeSpaceCount = CALCULATE(COUNT('Build_Capacity'[RangeSpace]))
VAR TotalCount = CALCULATE(DISTINCTCOUNT('Build_Usage'[ID]), ALL('Date'))
VAR BLANKK = BLANK()

RETURN
IF (
ISFILTERED('Hs'[House Name]) && RangeSpaceCount = TotalCount,
varTest,
BLANKK
)

 

AntrikshSharma
Super User
Super User

@Tini-Bee What is the issue here?

bhanu_gautam
Super User
Super User

@Tini-Bee Try using

DAX
SumRangeSpace =
VAR varTest = CALCULATE(MAX([SumRangeSpace1]), ALL('Date'))
VAR RangeSpaceCount = CALCULATE(COUNT('Build_Capacity'[RangeSpace]))
VAR TotalCount = CALCULATE(DISTINCTCOUNT('Build_Usage'[ID]), ALL('Date'))
VAR BLANKK = BLANK()

RETURN
IF (
ISFILTERED('Hs'[House Name]) && RangeSpaceCount = TotalCount,
[SumRangeSpace1],
BLANKK
)

 




Did I answer your question? Mark my post as a solution! And Kudos are appreciated

Proud to be a Super User!




LinkedIn






Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code FABINSIDER for a $400 discount!

FebPBI_Carousel

Power BI Monthly Update - February 2025

Check out the February 2025 Power BI update to learn about new features.

March2025 Carousel

Fabric Community Update - March 2025

Find out what's new and trending in the Fabric community.

Top Solution Authors
Top Kudoed Authors