# Count your top selling products with DAX

One of my customers wanted to count their top selling products. Since sharing is caring I will show you how this can be achived in this post ðŸ™‚

I want to list all products that sold for at least 5000 dollars in the year 2005.

```DEFINE
MEASURE 'Internet Sales'[Sales] =
SUM ( 'Internet Sales'[Sales Amount] )
EVALUATE
FILTER (
SUMMARIZE (
CROSSJOIN (
VALUES ( 'Date'[Calendar Year] ),
VALUES ( 'Product'[Product Id] )
),
'Date'[Calendar Year],
'Product'[Product Id]
),
'Internet Sales'[Sales] >= 5000
&& 'Date'[Calendar Year] = 2005
),
"Sum of Sales", 'Internet Sales'[Sales]
) ORDER BY 'Internet Sales'[Sales] DESC
```

And the result will look like this

If you want to only display the number of products you can add “row” and “countrows” like this

```DEFINE
MEASURE 'Internet Sales'[Sales] =
SUM ( 'Internet Sales'[Sales Amount] )
EVALUATE
ROW (
"Count", COUNTROWS (
FILTER (
SUMMARIZE (
CROSSJOIN (
VALUES ( 'Date'[Calendar Year] ),
VALUES ( 'Product'[Product Id] )
),
'Date'[Calendar Year],
'Product'[Product Id]
),
'Internet Sales'[Sales] >= 5000
&& 'Date'[Calendar Year] = 2005
),
"Sum of Sales", 'Internet Sales'[Sales]
)
)
)
```

And the result will look like this

# What is the difference between “non empty” and “nonempty” in MDX?

In this post I will demonstrate the difference between “non empty” and “nonempty” in MDX. Not everyone knows the difference between these functions.

Lets write a simple MDX

```SELECT {
[Measures].[Reseller Sales Amount],
[Measures].[Discount Amount]
} ON COLUMNS,
[Geography].[City].[City] ON ROWS
```

And the result will look like this

As you can see there are cities with no sales and no discount. If I add the “NONEMPTY” function I will remove all rows where both measures are NULL.

```SELECT {
[Measures].[Reseller Sales Amount],
[Measures].[Discount Amount]
} ON COLUMNS,
NON EMPTY [Geography].[City].[City] ON ROWS
```

But to remove all rows with NULL in discount amount I must use the “nonempty”. To make this work I will have to create a new measure that prints NULL if the value are 0 ðŸ™‚

```WITH MEMBER [Measures].[Discount Amount2]
AS
IIF([Measures].[Discount Amount] = 0, NULL, [Measures].[Discount Amount])

SELECT {
[Measures].[Reseller Sales Amount],
[Measures].[Discount Amount2]
} ON COLUMNS,
NONEMPTY ([Geography].[City].[City], [Measures].[Discount Amount2]) ON ROWS
```

The “NONEMPTY” and “NON EMPTY” can be replaced by a filter. Like this

```WITH MEMBER [Measures].[Discount Amount2]
AS
IIF([Measures].[Discount Amount] = 0, NULL, [Measures].[Discount Amount])

SELECT {
[Measures].[Reseller Sales Amount],
[Measures].[Discount Amount2]
} ON COLUMNS,
FILTER ([Geography].[City].[City], NOT IsEmpty([Measures].[Discount Amount2])) ON ROWS
```

# Identify products with no sale in MDX

To identify products with no sales in MDX we can use the isBlank function and a filter.

Here is a short sample

```SELECT [Measures].[Internet Sales Amount] ON 0,
FILTER(
[Product].[Product].[Product],
ISEMPTY([Measures].[Internet Sales Amount])
) ON 1
```

And if you want to have products with sales you can use the NON EMPTY function.

```SELECT [Measures].[Internet Sales Amount] ON 0,
NON EMPTY [Product].[Product].[Product] ON 1
```

# Identify products with no sale in DAX

To identify products with no sale in DAX we can use the ISBLANK function.

Here is a sample

```EVALUATE
FILTER (
SUMMARIZE (
'Product',
'Product'[Product Name],
"Sales", 'Internet Sales'[Internet Total Sales]
),
ISBLANK ( 'Internet Sales'[Internet Total Sales] ) = TRUE
)
```

And if you want the products with sale you can switch “TRUE” with “FALSE”

```EVALUATE
FILTER (
SUMMARIZE (
'Product',
'Product'[Product Name],
"Sales", 'Internet Sales'[Internet Total Sales]
),
ISBLANK ( 'Internet Sales'[Internet Total Sales] ) = FALSE
)
```

# Get first date with sales and last date with sales in MDX

To get the first date with sales and last date with sales we can use the “tail” and the “head” function in MDX. These functions are quite handy if you want to have a default date in your reports.

Here is the code:

```WITH MEMBER [Last Date With Sales] AS
Tail (
Filter ( [Date].[Calendar].[Date].Members, [Measures].[Internet Sales Amount]  NULL ),
1
).Item ( 0 ) .MEMBER_CAPTION
MEMBER [First Date With Sales] AS
Filter ( [Date].[Calendar].[Date].Members, [Measures].[Internet Sales Amount]  NULL ),
1
).Item ( 0 ) .MEMBER_CAPTION
SELECT
{ [Measures].[First Date With Sales], [Measures].[Last Date With Sales] } ON 0
```

And the result will look like this

# One of my favourite “Mosha tricks”. Fast count in MDX

I am quite sure that many of you MDX nerds have been reading articles and tips on Moshas old blog (http://sqlblog.com/blogs/mosha/). Mosha was one of the inventors of MDX.

If you are struggling with MDX performance take a look on Moshas blog.

One of my favourite trick on this blog is the alternative way of counting records in MDX. Off course you have the regular way using the COUNT function.

```WITH MEMBER [Number Of Products] AS
Count ( Filter ( [Product].[Product Name].[Product Name], [Measures].[Internet Total Sales] ) )
SELECT
{ [Measures].[Internet Total Sales], [Measures].[Number Of Products] } ON 0,
NON EMPTY Order ( [Geography].[City].[City], [Measures].[Internet Total Sales], DESC ) ON 1
FROM [Internet Sales]
```

The problem with this is that it does not evaluate as bulk mode. This will make the query run really slow.

One way of making this query faster is to use the SUM function with an IIF test.

```WITH MEMBER [Number Of Products] AS
( Sum (
[Product].[Product Name].[Product Name].Members,
IIf ( [Measures].[Internet Total Sales] > 0, 1, NULL )
) )
SELECT
{ [Measures].[Internet Total Sales], [Measures].[Number Of Products] } ON 0,
NON EMPTY Order ( [Geography].[City].[City], [Measures].[Internet Total Sales], DESC ) ON 1
FROM [Internet Sales]
```

This reduced the query time for one of my queries from about a minute to 4 seconds.

# Top products within each category in DAX

If you want to have top products within each category you can solve this using a quite simple query.

Below is a query that lists each model. And for each model the top 2 products (if there are 2 or more products in that model).

```DEFINE
MEASURE 'Product'[Sales] =
SUM ( 'Internet Sales'[Sales Amount] )
MEASURE 'Product'[Ranks] =
RANKX (
ALL ( 'Product'[Product Name] ),
SUMX ( RELATEDTABLE ( 'Internet Sales' ), [Sales Amount] )
)
EVALUATE
FILTER (
GENERATE (
VALUES ( 'Product'[Model Name] ),
TOPN (
2,
VALUES ( 'Product'[Product Name] ),
'Product'[Sales],
0
)
),
"Ranks", 'Product'[Ranks],
"SalesAmount", 'Product'[Sales]
),
[SalesAmount] <> 0
)
ORDER BY 'Product'[Model Name] ASC, 'Product'[Sales] DESC
```

Please note the filter that removes empty rows. If you remove the filterÂ the query will return weird results.

Here is what the result will look like

# How to use “RANKX”

If you want to rank your result in DAX you can use the RANKX function.

Below is a sample

```DEFINE
MEASURE 'Internet Sales'[Rank] =
RANKX (
ALL ( 'Product'[Product Name] ),
SUMX (
RELATEDTABLE ( 'Internet Sales' ),
'Internet Sales'[Internet Total Sales]
)
)
EVALUATE
FILTER (
VALUES ( 'Product'[Product Name] ),
"Product Rank", 'Internet Sales'[Rank],
"Sales amount", 'Internet Sales'[Internet Total Sales]
),
[Sales amount] <> 0
)
ORDER BY 'Internet Sales'[Rank]
```

Please note the filter that removes the empty rows.

Here is the resultset

# 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]
```

# 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.