Category Archives: Basic script

Define a measure. Make your code more readable

Posted on by • Posted in Basic script • Tagged , , , • Leave a comment

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.

“Distinctcount” in DAX and MDX

Posted on by • Posted in Basic script • Tagged , , , , • Leave a comment

If you want to count distinct values in DAX you have at least two ways of doing this. You can use the distinctcount or you can use countrows with distinct.

A sample can be viewed below

EVALUATE
SUMMARIZE (
    'Product',
    "Number of products version 1", DISTINCTCOUNT ( 'Product'[Product Id] ),
    "Number of products version 2", COUNTROWS ( DISTINCT ( 'Product'[Product Id] ) )
)

In MDX the same thing can be done using this code

WITH MEMBER CountDistinct
AS COUNT(DISTINCT [Product].[Product Id].[Product Id])

SELECT {[Measures].[CountDistinct]} ON 0
FROM [Internet Operation]

You also have a distinctcount in MDX that can be used.

Add a subtotal with “Rollup”

Posted on by • Posted in Basic script • Tagged , , , • Leave a comment

If you use “Summarize” you can add a subtotal with the use of “Rollup”.

In the code below there is an example on how this can be done.

EVALUATE
FILTER (
    SUMMARIZE (
        'Internet Sales',
        ROLLUP ( 'Date'[Month Name] ),
        'Date'[Calendar Year],
        "Subtotal?", ISSUBTOTAL ( 'Date'[Month Name] ),
        "Sales", SUM ( 'Internet Sales'[Sales Amount] ),
        "Quantity", SUM ( 'Internet Sales'[Order Quantity] )
    ),
    'Date'[Calendar Year] = 2007
)

This query will give you sales and quantity per month. But you will also have a row with subtotal. To check if the row is a “Rollup” we can use the “ISSUBTOTAL”. You also have something called “Rollupgroup”.

In MDX you can write something like this to get a subtotal.

select {[Measures].[Internet Total Sales],[Measures].[Internet Total Units]} on 0,
[Date].[Calendar Year].&[2007] * [Date].[Month Name].AllMembers on 1
from [Internet Operation]

The subtotal will be in the “all” row. Depending on what you called the all level 🙂 You do not have any functionality like ISSUBTOTAL as far as I know.

Add a filter to your query using the “Filter” function

Posted on by • Posted in Basic script • Tagged , , , , • Leave a comment

It is really easy to add filters to your DAX query. In this post I will show you some samples.

Lets take a look at the following query:

EVALUATE
FILTER (
SUMMARIZE (
'Internet Sales',
'Date'[Calendar Year],
'Date'[Month Name],
"Sales", 'Internet Sales'[Internet Total Sales]
),
'Date'[Calendar Year] = 2007
)

This will give you sales per year per month for year 2007.

The MDX for the same resultset might look like this (you can of course move the year filter to the where clause)

SELECT [Measures].[Internet Total Sales] on 0,
[Date].[Calendar Year].&[2007] * [Date].[Month Name].[Month Name] on 1
from [Internet Operation]

In the next query I have added another filter

EVALUATE
(
FILTER(
SUMMARIZE
(
'Internet Sales',
'Date'[Calendar Year],
'Date'[Month Name],
"Sales", 'Internet Sales'[Internet Total Sales]
), 'Date'[Calendar Year] = 2007 && 'Internet Sales'[Internet Total Sales] > 500000
)
)

This new condition will remove all rows with sales less than 500000 for year 2007

The equal query in MDX will look like

SELECT [Measures].[Internet Total Sales] on 0,
FILTER([Date].[Calendar Year].&[2007] * [Date].[Month Name].[Month Name],[Measures].[Internet Total Sales] > 500000) on 1
from [Internet Operation]

The next sample will be a little more complex. It will give you sales per year per month compared to the years total. I am not sure if this is the most easy way of doing this. Or the best way 🙂

DEFINE
MEASURE 'Internet Sales'[Sum Year] =
CALCULATE (
'Internet Sales'[Internet Total Sales],
ALLSELECTED ()
)
MEASURE 'Internet Sales'[Percent of year] =
FORMAT (
CALCULATE (
'Internet Sales'[Internet Total Sales]
/ 'Internet Sales'[Sum Year]
),
"Percent"
)
EVALUATE
ADDCOLUMNS (
FILTER (
SUMMARIZE (
'Internet Sales',
'Date'[Calendar Year],
'Date'[Month Name],
"Sales", 'Internet Sales'[Internet Total Sales]
),
'Date'[Calendar Year] = 2007
),
"Year", 'Internet Sales'[Sum Year],
"Percent of year", 'Internet Sales'[Percent of year]
)

The get a similar resultset in MDX we can use this query

WITH MEMBER [Measures].[Sum Year] AS
( Root ( [Date].[Calendar Year] ),
[Measures].[Internet Total Sales] )
MEMBER [Measures].[Percent of year] AS
( [Measures].[Internet Total Sales] / [Measures].[Sum Year] ),
FORMAT_STRING = 'Percent'
SELECT
{ [Measures].[Internet Total Sales], [Measures].[Sum Year], [Measures].[Percent of year] } ON 0,
[Date].[Calendar].[Year].&[2007] * [Date].[Month Name].Children ON 1
FROM [Internet Operation]

Evaluate a table expression using “calculatetable”

Posted on by • Posted in Basic script • Tagged , • Leave a comment

In this post I will give you some samples on how to use the “calculatetable” function. In MSDN it is described as “Evaluates a table expression in a context modified by the given filters”. This function will always return all the columns in the table. And you have the possibility to add new columns and add filters.

Lets take a look at some examples:

EVALUATE
CALCULATETABLE ( 'Internet Sales' )

The code above will basically give you all the columns and all the rows in the “Internet Sales” table. This will be equal to a “select * from table” in SQL.

In the next sample I will show you how to filter the resultset in “calculatetable”

EVALUATE
CALCULATETABLE (
    'Internet Sales',
    'Date'[Calendar Year] = 2005,
    'Internet Sales'[ProductKey] = 314
)

As you can see here I have added filters for year 2005 and productkey 314.

Aggregate using “summarize”

Posted on by • Posted in Basic script • Tagged , , • Leave a comment

In DAX we have a function called “summarize”. This function aggregates totals over a set of groups.

Here is an example on how this function can be used:

 
EVALUATE
SUMMARIZE (
    'Internet Sales',
    'Date'[Calendar Year],
    'Date'[Month Name],
    "Sales", SUM ( 'Internet Sales'[Sales Amount] ),
    "Quantity", SUM ( 'Internet Sales'[Order Quantity] )
)

The code above will give you sales and quantity per month per year. The first argument is the table name. The second and third argument is the columns we want to order by. Fourth and fifth argument is the numbers you want to aggregate.

If you want to use some of the predefined measures you can skip the sum and write like this:

EVALUATE
SUMMARIZE (
    'Internet Sales',
    'Date'[Calendar Year],
    'Date'[Month Name],
    "Sales", 'Internet Sales'[Internet Total Sales],
    "Quantity", 'Internet Sales'[Internet Total Units]
)

If you manually want to set the order by clause this can be set like this:

EVALUATE
SUMMARIZE (
    'Internet Sales',
    'Date'[Calendar Year],
    'Date'[Month Name],
    "Sales", 'Internet Sales'[Internet Total Sales],
    "Quantity", 'Internet Sales'[Internet Total Units]
)
ORDER BY 'Date'[Month Name], 'Date'[Calendar Year]

The MDX that will give you the same result can look like this:

SELECT NON EMPTY [Date].[Calendar Year].[Calendar Year] * [Date].[Month Name].[Month Name] ON 1,
{ [Measures].[Internet Total Sales], [Measures].[Internet Total Units] } ON 0
FROM [Internet Operation]

And the SQL to get the similar resulat might look like this:

SELECT dimdate.calendaryear, 
       dimdate.englishmonthname, 
       Sum([salesamount])   AS 'Internet Total Sales', 
       Sum([orderquantity]) AS 'Internet Total Units' 
FROM   [factinternetsales] 
       INNER JOIN dimdate 
               ON dimdate.datekey = [factinternetsales].[orderdatekey] 
GROUP  BY dimdate.calendaryear, 
          dimdate.monthnumberofyear, 
          dimdate.englishmonthname

The result will be like this:

Summarize

Creating our first DAX script using “evaluate”

Posted on by • Posted in Basic script • Leave a comment

To create our first DAX script we can start with one of the easiest functions. In DAX you have a function that is called “evaluate”. This function is used to retrieve data from a specific table. No aggregation is performed.

To run this query you can start SQL Server Management Studio and then create a new MDX query (yes MDX. Even though you want to write DAX).

Paste the following query:

evaluate Product

This will give you a result with all the data in the Product table. It will give you the same result as a “select * from Product”

.Evaulate

If you want query a table with spaces in the name you will have to add single quotes.

evaluate 'Product Subcategory'