Referencing Measures within CALCULATE() & FILTER()

Anything related to PowerPivot and DAX Formuale
gillyd123
Posts: 14
Joined: Fri Aug 26, 2016 8:09 am

Referencing Measures within CALCULATE() & FILTER()

Postby gillyd123 » Sun May 24, 2020 5:24 am

Hi

Why is this not working:

Highest Price = MAX( Products[ListPrice] )

Transactions for Highest Price BROKEN

= CALCULATE(
COUNTROWS(Sales),
FILTER(
Products,
Products[ListPrice] = [Highest Price]
)
)

However, the following will work

Transaction for Highest Price WORKING

= CALCULATE(
COUNTROWS(Sales),
FILTER(
Products,
Products[ListPrice] = MAX( Products[ListPrice] )
)
)

Any help would be much appreciated.

G

MattAllington
Posts: 1091
Joined: Sun May 04, 2014 4:01 pm
Location: Sydney, Australia

Re: Referencing Measures within CALCULATE() & FILTER()

Postby MattAllington » Sun May 24, 2020 7:45 am

It’s complicated. The 2 formulas are not the same, even though they look the same. The first one is actually

= CALCULATE(
COUNTROWS(Sales),
FILTER(
Products,
Products[ListPrice] = CALCULATE(MAX( Products[ListPrice] ))
)
)

A measure contains an implicit calculate which forces context transition when used within a function with a row context.
Matt Allington is Self Service BI Consultant, Trainer and Author of the Book "Supercharge Power BI".
Power BI Training


Return to “PowerPivot/DAX”

Who is online

Users browsing this forum: No registered users and 4 guests