Home > DAX > DAX – TOPN, GENERATE

DAX – TOPN, GENERATE

It is a very common scenario to have top n record set and for each records top n record set in any programming language like MDX, TSQL, LINQ and C#.

Here planning to discuss the same concept using DAX functions.

1. Retrieve all the countries with Internet Sales in DESC order as

EVALUATE (
summarize( 'Internet Sales'
, 'Geography'[Country Region Name]
//, 'Geography'[City]
, "InternetSalesAmount"
, FORMAT(SUM('Internet Sales'[Sales Amount]),"currency")
)
)
order by 'Internet Sales'[Internet Total Sales] desc



2. Retrieve all the countries including Cities from countries as

EVALUATE (
summarize( 'Internet Sales'
, 'Geography'[Country Region Name]
, 'Geography'[City]
, "InternetSalesAmount"
, FORMAT(SUM('Internet Sales'[Sales Amount]),"currency")
)
)
order by 'Geography'[Country Region Name] asc
,'Internet Sales'[Internet Total Sales] desc



3. Retrieve top 2 countries using topn function and also used addcolumns to make a dataset using different columns including a derived column as

DEFINE MEASURE'Geography'[Sales] = sum('Internet Sales'[Sales Amount])
EVALUATE (
addcolumns(
topn(
2
,values(Geography[Country Region Name])
, 'Geography'[Sales]
, 0
)
,"SalesAmount"
, format('Geography'[Sales],"currency")
)
)
order by 'Geography'[Sales] desc



4. Retrieve for every country top 2 cities with Internet Sales as

DEFINE MEASURE'Geography'[Sales] = sum('Internet Sales'[Sales Amount])
EVALUATE (
addcolumns(
GENERATE(
values('Geography'[Country Region Name])
, topn(
2 
, values('Geography'[City])
, 'Geography'[Sales]
, 0
) 
)
,"SalesAmount"
, format('Geography'[Sales],"currency")
)
)
order by 
'Geography'[Country Region Name] asc
, 'Geography'[Sales] desc




Points to consider:

1. Considered few common scenarios in any reporting requirements
2. It could be great to add RANK function to GENERATE, which I am planning write in next DAX posts.

Advertisements
Categories: DAX Tags:
  1. j ohara
    June 29, 2012 at 4:22 pm

    Hi there

    Please could you explain how to do this inside the SSAS tabular mode built into sql server data tools?
    evaluate command does not exist -i am looking to publish to powerview and restrict the top10 results in a table or matrix

    • June 30, 2012 at 12:44 pm

      Hello j ohara,
      In SSAS tabular model, please create a new Rank column and give the calculation expression with your business logic. Use this Rank into Powerview filter to restrict top 10 results. Please let me know if requires further details.

      — Prav

  1. April 11, 2013 at 4:59 am

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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s

%d bloggers like this: