Define a measure. Make your code more readable

To make your code more readable you can create measures outside the evaluate statement. Let’s see an example on how we can do this.

Take a look at the query that returns sales per year versus sales last year.

EVALUATE
FILTER (
SUMMARIZE (
'Date',
'Date'[Calendar Year],
"Sales this year", SUM ( 'Internet Sales'[Sales Amount] ),
"Sales last year", CALCULATE(SUM ( 'Internet Sales'[Sales Amount] ),
SAMEPERIODLASTYEAR ( 'Date'[Date] ))
),
[Sales last year] <> 0
)

To move the calculation for “Sales last year” you can define a measure outside the evaluate statement like below. This can be very useful if you have lots of logic inside your query.

DEFINE
MEASURE 'Internet Sales'[Sales last year] =
CALCULATE ( SUM ( 'Internet Sales'[Sales Amount] ), SAMEPERIODLASTYEAR ( 'Date'[Date] ))

EVALUATE
FILTER (
SUMMARIZE (
'Date',
'Date'[Calendar Year],
"Sales this year", SUM ( 'Internet Sales'[Sales Amount] ),
"Sales last year", 'Internet Sales'[Sales last year]
),
[Sales last year] <> 0
)

If you want more than one measure you just add new measures below the first one. But the “DEFINE” should just written once at the top.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s