Get Top N results in DAX

To get Top N results in DAX we can use the TOPN function.

EVALUATE
TOPN (
    10,
    SUMMARIZE (
        'Internet Sales',
        'Product'[Product Name],
        "Sales Amount", 'Internet Sales'[Internet Total Sales]
    ),
    'Internet Sales'[Internet Total Sales]
)
ORDER BY 'Internet Sales'[Internet Total Sales] DESC

A similar MDX query might look like this

SELECT TOPCOUNT(
	[Product].[Product Name].[Product Name],
	10, 
	[Measures].[Internet Total Sales]) 
ON 1,
[Measures].[Internet Total Sales] ON 0
FROM [Internet Sales]

If you want the bottom N results you can write a DAX like this

EVALUATE
TOPN (
    10,
    SUMMARIZE (
        'Internet Sales',
        'Product'[Product Name],
        "Sales Amount", 'Internet Sales'[Internet Total Sales]
    ),
    'Internet Sales'[Internet Total Sales],
    1
)
ORDER BY 'Internet Sales'[Internet Total Sales] ASC

In MDX we replace the TOPCOUNT with the BOTTOMCOUNT. And then we add a filter to avoid empty rows

SELECT BOTTOMCOUNT(
	FILTER(
		[Product].[Product Name].[Product Name], 
		NOT ISEMPTY([Measures].[Internet Total Sales])
		),
	10, 
	[Measures].[Internet Total Sales])
ON 1,
[Measures].[Internet Total Sales] ON 0
FROM [Internet Sales]
Advertisements

Paging in DAX using “Start At”

If you want to use paging in your DAX query you can do this by using the “Start At” parameter in “Summarize”.

Here is a sample on how this can be done:

EVALUATE
SUMMARIZE (
'Date',
'Date'[Month Name],
'Date'[Calendar Year],
"Sales", 'Internet Sales'[Internet Total Sales]
)
ORDER BY 'Date'[Calendar Year]
START AT 2007

The code above will give sales per year per month starting at the year of 2007. Please note that this also returns empty rows. In the next sample we remove the blank rows and extend our “Start At” to also handle month names.

EVALUATE
FILTER (
SUMMARIZE (
'Date',
'Date'[Month Name],
'Date'[Calendar Year],
"Sales", 'Internet Sales'[Internet Total Sales]
),
'Internet Sales'[Internet Total Sales] <> 0
)
ORDER BY 'Date'[Calendar Year], 'Date'[Month Name]
START AT 2007,
"July"

This query will give sales per year per month for year later than 2007 and month name later than july. Blank rows will also be removed using the filter function.

Creating an simple if test in DAX

Here is a sample on how to create an if test in DAX. It is really easy 🙂 In the sample below we test the sales per month for 2007 versus the sales per month for 2006. If the sales are higher we print “Higher”. If it was lower we print “Lower”.

DEFINE
    MEASURE 'Internet Sales'[Previous Sales per month] =
        CALCULATE (
            'Internet Sales'[Internet Total Sales],
            SAMEPERIODLASTYEAR ( 'Date'[Date] )
        )
    MEASURE 'Internet Sales'[Sales per month] =
        CALCULATE (
            'Internet Sales'[Internet Total Sales],
            'Date'[Month]
        )
    MEASURE 'Internet Sales'[MyTest] =
        IF (
            'Internet Sales'[Sales per month]
                >= 'Internet Sales'[Previous Sales per month],
            "Higher",
            "Lower"
        )
EVALUATE
FILTER (
    SUMMARIZE (
        'Date',
        'Date'[Month],
        'Date'[Calendar Year],
        "Previous Sales per month", FORMAT (
            'Internet Sales'[Previous Sales per month],
            "Currency"
        ),
        "Sales Sales per month", FORMAT ( 'Internet Sales'[Sales per month], "Currency" ),
        "Change", 'Internet Sales'[MyTest]
    ),
    'Date'[Calendar Year] = 2007
)

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.

“Distinctcount” in DAX and MDX

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”

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

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]