Tag Archives: Filter

Count your top selling products with DAX

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

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
        ADDCOLUMNS (
            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

Top selling 1

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 (
        ADDCOLUMNS (
            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

Top selling 2

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

Posted on by • Posted in MDX • Tagged , , , • Leave a comment

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 
 FROM [Adventure Works]

And the result will look like this

nonempty 1

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 
 FROM [Adventure Works]

nonempty 2

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 
 FROM [Adventure Works]

nonempty 3

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 
 FROM [Adventure Works]

Identify products with no sale in MDX

Posted on by • Posted in MDX • Tagged , • Leave a comment

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
FROM [Adventure Works]

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
FROM [Adventure Works]

Identify products with no sale in DAX

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

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

Posted on by • Posted in MDX • Tagged , ,

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
  Head (
    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
FROM [Adventure Works]

And the result will look like this

Head and tail

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

Posted on by • Posted in MDX • Tagged , , , • Leave a comment

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

Posted on by • Posted in Advanced • Tagged , , , , • Leave a comment

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 (
ADDCOLUMNS (
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

Rank advanced

How to use “RANKX”

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

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 (
ADDCOLUMNS (
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

Rank

Get Top N results in DAX

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

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”

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.