Get percent of total in DAX using ALLSELECTED()

To get percent of total in DAX we can use the ALLSELECTED function. To demonstrate how this can be achieved I will make 3 calculated measures in my model.

1. TotalSales:=CALCULATE(SUM([Order Quantity]);ALLSELECTED())
2. Sum Order Quantity:=Sum([Order Quantity])
3. Percent Of Total:=[Sum Order Quantity] / [TotalSales]

The first measure will give the total quantity. The second one will give sum quantity for each dimension member. The last one will give the percent. To make the last one nice and readable you can format it as percent.

This is how it will look in an Excel pivot

Percent of total

This easy approach will only work on the first level in a hierarchy. To make it work on multiple levels you have to extend the code. But that will be an other post ūüôā

Get number of days since last sales in DAX

Today I want to show you an sample on how to count the number of days since last sales. And the number of days between first sale and last sale.

The code is quite easy to understand

DEFINE
    MEASURE 'Internet Sales'[First order date] =
        CALCULATE ( MIN ( 'Internet Sales'[Order Date] ) )
    MEASURE 'Internet Sales'[Last order date] =
        CALCULATE ( MAX ( 'Internet Sales'[Order Date] ) )
EVALUATE
 (
    FILTER (
        SUMMARIZE (
            'Geography',
            'Geography'[City],
            "First order", 'Internet Sales'[First order date],
            "Last order", 'Internet Sales'[Last order date],
            "Days between", 1 * 'Internet Sales'[Last order date]
                - 'Internet Sales'[First order date],
            "Days since last order", 1 * TODAY ()
                - 'Internet Sales'[Last order date],
            "Sales", FORMAT (
                'Internet Sales'[Internet Total Sales],
                "Currency"
            )
        ),
        'Internet Sales'[Internet Total Sales] <> 0
    )
)
ORDER BY 'Internet Sales'[Last order date] DESC

And the result will look like this

Count days 1

A geek will always think; “how can I make this query run faster?”

One thing is to replace summarize with addcolumns. As in the sample below.

DEFINE
    MEASURE 'Internet Sales'[First order date] =
        CALCULATE ( MIN ( 'Internet Sales'[Order Date] ) )
    MEASURE 'Internet Sales'[Last order date] =
        CALCULATE ( MAX ( 'Internet Sales'[Order Date] ) )
EVALUATE
 (
    FILTER (
        ADDCOLUMNS (
            VALUES ( 'Geography'[City] ),
            "First order", 'Internet Sales'[First order date],
            "Last order", 'Internet Sales'[Last order date],
            "Days between", 1 * 'Internet Sales'[Last order date]
                - 'Internet Sales'[First order date],
            "Days since last order", 1 * TODAY ()
                - 'Internet Sales'[Last order date],
            "Sales", FORMAT (
                'Internet Sales'[Internet Total Sales],
                "Currency"
            )
        ),
        'Internet Sales'[Internet Total Sales] <> 0
    )
)
ORDER BY 'Internet Sales'[Last order date] DESC

If you check the execution time in SQL Server Profiler you will see that query 2 runs faster than query 1.

Getting started with aggregations in SSAS multidimensional

In this post I will describe how to get started with aggregations in SSAS multidimensional. Since this is a rather complicated thing I will start with a really basic sample.

First I will modify my Adventure Works database. I will create a new aggregation design with no aggregations. This can be done by running the aggregation wizard and selecting ‚ÄúNo aggregations‚ÄĚ. Then save the aggregation design and assign it to the partitions.

It will look like something like this

Aggregations 1

Do a full process on the measure group

Then run a test query against this cube using one of the measures from that measure group

SELECT [Measures].[Internet Sales Amount] ON 0,
NON EMPTY [Product].[Subcategory].[Subcategory] ON 1
FROM [Adventure Works]
WHERE [Date].[Calendar].[Calendar Year].&[2007]

Subscube

As you can see here I get a query subcube event. I only get one of these events because this query is really simple. For more complex queries you get loads of them. In a typical production environment you will get loads of them before the aggregations are optimized.

My main goal now is to eliminate this subcube event by designing aggregations.

To do this I open the aggregations. Then chooses ‚ÄúAdvanced view‚ÄĚ. And then ‚ÄúAggregationDesign EMPTY‚ÄĚ

Aggregations 2

Right click in the gray area in the middle and choose ‚ÄúNew aggregation‚ÄĚ

In the check boxes check the following. Product -> Subcategory and Date -> Calendar Year

Aggregations 3

Then save and do a full process

Re-run the previous query and watch profiler

Aggregations 4

Now you can see that my query did hit the aggregation.

If you choose to run the queries multiple times on each step make sure you clear the cache in between.

SSAS multidimensional: Are you sure your aggregations are okay?

A few days back I wanted to optimize a SSAS multidimensional cube. I was testing different queries to see if any of them used my aggregations. As I understand SSAS multidimensional provides results on different ways.

    1. From cache
    2. From aggregations
    3. Build the result from storage engine / formula engine

Number 1 is the fastest way and number 3 is the slowest. So it’s quite important for query performance that you have well considered aggregations. Please note that aggregations adds time to cube processing. When I saw that my queries did not hit any aggregations I started to Google around. And when reading some forum posts I started to suspect that my aggregations was unprocessed. I was running a query similar to this to check the size of my aggregations

SELECT * FROM SystemRestrictSchema($system.discover_partition_stat ,
DATABASE_NAME = 'Adventure Works' ,
CUBE_NAME = 'Adventure Works' ,
MEASURE_GROUP_NAME = 'Internet Sales' ,
PARTITION_NAME = 'Internet_Sales_2013')

Aggregations

This result looks to be okay. But the result from my cube was that almost every size was set to 0. How can I reproduce and identify this issue? I did read the documentation on processing options and figured out that “all flexible relationships we be dropped when performing a process update on a dimension”.

To figure out what was going on I tried to change a value in a dimension with a flexible relations and then run a process update on the dimension. Then followed by a full process of my test partition.

Aggregations 2

Now you can see that most of the size is set to 0. How can I get them back now? Process partion / measure group / cube with “Process index”. And the your aggregations should be fine. If you run “Process default” on your dimension or full process on the cube the aggregations should be fine.

I will certainly check my processing routines after this ūüôā Does anyone have any comments on this?

Get YTD two years back in DAX

In this sample I will show you how you can get YTD (year to date), LYTD (last year to date) and even YTD for two years back or more. When getting YTD we will use the TotalYTD function. And for LYTD we will use TotalYTD in combination with SAMEPERIODLASTYEAR. But if you want YTD for 2 years back you have to add even additional functions.

Lets see the query

DEFINE
    MEASURE 'Internet Sales'[Previous Year To Date] =
        CALCULATE (
            TOTALYTD (
                'Internet Sales'[Internet Total Sales],
                SAMEPERIODLASTYEAR ( 'Date'[Date] )
            )
        )
    MEASURE 'Internet Sales'[Year To Date] =
        CALCULATE (
            TOTALYTD (
                'Internet Sales'[Internet Total Sales],
                'Date'[Date]
            )
        )
    MEASURE 'Internet Sales'[Year To Date 2 year] =
        CALCULATE (
            TOTALYTD (
                'Internet Sales'[Internet Total Sales],
                DATESBETWEEN (
                    'Date'[Date],
                    DATEADD ( FIRSTDATE ( 'Date'[Date] ), -2, YEAR ),
                    DATEADD ( LASTDATE ( 'Date'[Date] ), -2, YEAR )
                )
            )
        )
    MEASURE 'Internet Sales'[Change] =
        FORMAT (
            'Internet Sales'[Year To Date]
                / 'Internet Sales'[Previous Year To Date],
            "Percent"
        )
EVALUATE
FILTER (
    SUMMARIZE (
        'Date',
        'Date'[Date],
        "Previous Year To Date", 'Internet Sales'[Previous Year To Date],
        "Sales Year To Date", 'Internet Sales'[Year To Date],
        "Change YTD vs LYTD", 'Internet Sales'[Change],
        "Year to date 2 years ago", 'Internet Sales'[Year To Date 2 year]
    ),
    'Date'[Date] = DATE ( 2008, 01, 15 )
)

And the output will look like this

YTD 2 years back

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

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

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
)

Hierarchy testing in DAX (attempt number 2)

Here is some new query samples on how DAX hierarchies work. And this time it will work ūüôā

Id did a little modification of the model. I had to make a new hierachy in the employee table.

Hierachy in tabular model

Then you will have to deploy the model to the server.

My sample query looks like this:

DEFINE
    MEASURE 'Employee'[Employeelevel] =
        PATHLENGTH (
            PATH ( Employee[EmployeeKey], Employee[ParentEmployeeKey] )
        )
    MEASURE 'Employee'[ParentEmployee] =
        LOOKUPVALUE (
            'Employee'[Last Name],
            'Employee'[EmployeeKey], PATHITEMREVERSE (
                PATH (
                    'Employee'[EmployeeKey],
                    'Employee'[ParentEmployeeKey]
                ),
                2,
                1
            )
        )
    MEASURE 'Employee'[PathReverse] =
        PATHITEMREVERSE (
            PATH (
                'Employee'[EmployeeKey],
                'Employee'[ParentEmployeeKey]
            ),
            2,
            1
        )
    MEASURE 'Employee'[Path] =
        PATH (
            'Employee'[EmployeeKey],
            'Employee'[ParentEmployeeKey]
        )
EVALUATE
FILTER (
    SUMMARIZE (
        'Employee',
        'Employee'[EmployeeKey],
        'Employee'[Last Name],
        "Sales", 'Reseller Sales'[Reseller Total Sales],
        "Path", 'Employee'[Path],
        "PathItemReverse", 'Employee'[PathReverse],
        "Employeelevel", 'Employee'[Employeelevel],
        "ParentEmployee", 'Employee'[ParentEmployee]
    ),
    ISBLANK ( 'Reseller Sales'[Reseller Total Sales] ) = FALSE
)
ORDER BY 'Employee'[ParentEmployee], 'Employee'[EmployeeKey]

And the result looks like this

hierarchy correct

To verify the result I made an Excel pivot.

Testing some hierarchy stuff in DAX

Tonight I wanted to test some of the hierarchy functions in DAX. But after some testing I got very frustrated…

I have this sample DAX query

EVALUATE
FILTER (
    SUMMARIZE (
¬†¬†¬†¬†¬†¬†¬†¬†‘Product¬†SubCategory’,
¬†¬†¬†¬†¬†¬†¬†¬†‘Product¬†SubCategory'[Product¬†SubCategory¬†Name],
¬†¬†¬†¬†¬†¬†¬†¬†“Sales”,¬†‘Internet¬†Sales'[Internet¬†Total¬†Sales],
¬†¬†¬†¬†¬†¬†¬†¬†“Path”,¬†PATH¬†(
¬†¬†¬†¬†¬†¬†¬†¬†¬†¬†¬†¬†‘Product¬†SubCategory'[Product¬†Subcategory¬†Id],
¬†¬†¬†¬†¬†¬†¬†¬†¬†¬†¬†¬†‘Product¬†SubCategory'[Product¬†Category¬†Id]
        ),
¬†¬†¬†¬†¬†¬†¬†¬†“PathItem”,¬†PATHITEM¬†(
            PATH (
¬†¬†¬†¬†¬†¬†¬†¬†¬†¬†¬†¬†¬†¬†¬†¬†‘Product¬†SubCategory'[Product¬†Subcategory¬†Id],
¬†¬†¬†¬†¬†¬†¬†¬†¬†¬†¬†¬†¬†¬†¬†¬†‘Product¬†SubCategory'[Product¬†Category¬†Id]
            ),
            2,
            1
        ),
¬†¬†¬†¬†¬†¬†¬†¬†“PathItemReverse”,¬†PATHITEMREVERSE¬†(
            PATH (
¬†¬†¬†¬†¬†¬†¬†¬†¬†¬†¬†¬†¬†¬†¬†¬†‘Product¬†SubCategory'[Product¬†Subcategory¬†Id],
¬†¬†¬†¬†¬†¬†¬†¬†¬†¬†¬†¬†¬†¬†¬†¬†‘Product¬†SubCategory'[Product¬†Category¬†Id]
            ),
            2,
            1
        ),
¬†¬†¬†¬†¬†¬†¬†¬†“Product¬†Category¬†Name”,¬†LOOKUPVALUE¬†(
¬†¬†¬†¬†¬†¬†¬†¬†¬†¬†¬†¬†‘Product¬†Category'[Product¬†Category¬†Name],
¬†¬†¬†¬†¬†¬†¬†¬†¬†¬†¬†¬†‘Product¬†Category'[Product¬†Category¬†Id],¬†PATHITEMREVERSE¬†(
                PATH (
¬†¬†¬†¬†¬†¬†¬†¬†¬†¬†¬†¬†¬†¬†¬†¬†¬†¬†¬†¬†‘Product¬†SubCategory'[Product¬†Subcategory¬†Id],
¬†¬†¬†¬†¬†¬†¬†¬†¬†¬†¬†¬†¬†¬†¬†¬†¬†¬†¬†¬†‘Product¬†SubCategory'[Product¬†Category¬†Id]
                ),
                2,
                1
            )
        )
    ),
¬†¬†¬†¬†‘Internet¬†Sales'[Internet¬†Total¬†Sales]¬†<>¬†0
)
ORDER¬†BY¬†“Value¬†PathItem”¬†ASC

This produces this output

Hierarchy DAX

Except for the ordering this looks almost correct. But one row looks strange. The “Mountain Bikes” does not have any path at all.

If you pivot the same thing in Excel you will get this result.

Hierarchy Excel

Can anyone please help me with this problem?