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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
db2907
Helper II
Helper II

How the Filter definition of the metric can affect the final result.

Let's assume that I have the Products table in my model defined like this:

 

ProductIDUnitPriceUnitsInStockDiscontinued
11050TRUE
21530FALSE
32020TRUE
42510TRUE
5515FALSE

 

I have created two metrics :

 

Value1  = 

CALCULATE ( SUMX ( Products, Products[UnitPrice] * Products[UnitsInStock] ), FILTER ( Products, Products[Discontinued] = TRUE ) )

 

Value2 = 

CALCULATE ( SUMX ( Products, Products[UnitPrice] * Products[UnitsInStock] ), Products[Discontinued] = TRUE )

 

db2907_0-1686071686137.png

 

 

The only difference is the definition of the filter. I have evaluated the results in Power Desktop but I do not see any difference. This leads to my question: How could this definition affect the final result of the metric or when might this occur?

1 ACCEPTED SOLUTION
johnt75
Super User
Super User

The two measures are essentially identical, both are calculating the result for products which are discontinued. 

The definition for value2 is rewritten internally to be

Value2 =
CALCULATE (
    SUMX ( Products, Products[UnitPrice] * Products[UnitsInStock] ),
    FILTER ( ALL ( Products[Discontinued] ), Products[Discontinued] = TRUE )
)

The form of CALCULATE where you can specify a predicate as a filter rather than a table is merely syntax sugar, to make the code easier to read and write.

 

View solution in original post

1 REPLY 1
johnt75
Super User
Super User

The two measures are essentially identical, both are calculating the result for products which are discontinued. 

The definition for value2 is rewritten internally to be

Value2 =
CALCULATE (
    SUMX ( Products, Products[UnitPrice] * Products[UnitsInStock] ),
    FILTER ( ALL ( Products[Discontinued] ), Products[Discontinued] = TRUE )
)

The form of CALCULATE where you can specify a predicate as a filter rather than a table is merely syntax sugar, to make the code easier to read and write.

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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

Top Solution Authors