Using filters in DAX

In this post I will show some samples on different ways to use filtering in DAX. If you write ineffective filters you will most likely always run into performance issues. We will also see the difference between using SUMMARIZE() and ROW() in queries that only return one row.

Lets pretend one of your customers wants to know the sum of sales for the product “AWC Logo Cap”. And the minimum sales.

The benchmarking is done by running the different queries in DAX Studio multiple times. I have cleared cache between the executions.

Sample query 1.

DEFINE
    MEASURE 'FactInternetSales'[MyFilteredNumber] =
        CALCULATE (
            SUM ( FactInternetSales[UnitPrice] ),
            FILTER ( DimProduct, [EnglishProductName] = "AWC Logo Cap" )
        )
    MEASURE 'FactInternetSales'[MyFilteredNumberMin] =
        CALCULATE (
            MIN ( FactInternetSales[UnitPrice] ),
            FILTER ( DimProduct, [EnglishProductName] = "AWC Logo Cap" )
        )
EVALUATE
 (
    FILTER (
        SUMMARIZE (
            FactInternetSales,
            DimProduct[EnglishProductName],
            "Filtered result", 'FactInternetSales'[MyFilteredNumber],
            "Filtered result min", 'FactInternetSales'[MyFilteredNumberMin]
        ),
        'FactInternetSales'[MyFilteredNumber] > 0
    )
)

This query produces the correct answer. But is does not perform very well.

It took 32 MS.

Then I will change my filters to not filter the entire table. Only the column that we are interested in.
This is done by using VALUES(..) on the column name in the filter expression.

Sample query 2.

DEFINE
    MEASURE 'FactInternetSales'[MyFilteredNumber] =
        CALCULATE (
            SUM ( FactInternetSales[UnitPrice] ),
            FILTER (
                VALUES ( DimProduct[EnglishProductName] ),
                [EnglishProductName] = "AWC Logo Cap"
            )
        )
    MEASURE 'FactInternetSales'[MyFilteredNumberMin] =
        CALCULATE (
            MIN ( FactInternetSales[UnitPrice] ),
            FILTER (
                VALUES ( DimProduct[EnglishProductName] ),
                [EnglishProductName] = "AWC Logo Cap"
            )
        )
EVALUATE
 (
    FILTER (
        SUMMARIZE (
            FactInternetSales,
            DimProduct[EnglishProductName],
            "Filtered result", 'FactInternetSales'[MyFilteredNumber],
            "Filtered result min", 'FactInternetSales'[MyFilteredNumberMin]
        ),
        'FactInternetSales'[MyFilteredNumber] > 0
    )
)

The query took now 25 MS to execute

What if I move the filter above SUMMARIZE()?

Sample query 3.

DEFINE
    MEASURE 'FactInternetSales'[MyFilteredNumber] =
        CALCULATE ( SUM ( FactInternetSales[UnitPrice] ) )
    MEASURE 'FactInternetSales'[MyFilteredNumberMin] =
        CALCULATE ( MIN ( FactInternetSales[UnitPrice] ) )
EVALUATE
 (
    FILTER (
        SUMMARIZE (
            FactInternetSales,
            DimProduct[EnglishProductName],
            "Filtered result", 'FactInternetSales'[MyFilteredNumber],
            "Filtered result min", 'FactInternetSales'[MyFilteredNumberMin]
        ),
        'FactInternetSales'[MyFilteredNumber] > 0
            && [EnglishProductName] = "AWC Logo Cap"
    )
)

The query went down to 13 MS

What if I re-write my query to use ROW() instead?

Sample query 4.

EVALUATE
 (
    ROW (
        "Filtered result", CALCULATE (
            SUM ( FactInternetSales[UnitPrice] ),
            FILTER ( DimProduct,  [EnglishProductName] = "AWC Logo Cap" ) 
        ),
        "Count", CALCULATE (
            MIN ( FactInternetSales[UnitPrice] ),
            FILTER ( DimProduct, [EnglishProductName] = "AWC Logo Cap" ) 
        )
    )
 )

Then my query actually went down to 5 MS.

One last modification. Insert VALUES(..) to just apply the filter on the necessary column.

Sample query 5.

EVALUATE
 (
    ROW (
        "Filtered result", CALCULATE (
            SUM ( FactInternetSales[UnitPrice] ),
            FILTER (
                VALUES ( DimProduct[EnglishProductName] ),
                [EnglishProductName] = "AWC Logo Cap"
            )
        ),
        "Count", CALCULATE (
            MIN ( FactInternetSales[UnitPrice] ),
            FILTER (
                VALUES ( DimProduct[EnglishProductName] ),
                [EnglishProductName] = "AWC Logo Cap"
            )
        )
    )
)

And now we went down to about 4 MS.

The server timings for “Sample query 1” looks like this

server-timing-query-1

And the server timings for “Sample query 5” looks like this

server-timing-query-2

Advertisements

Search for dimension members in DAX using the “search” function

If you want to search for specific dimension members you can use the “Search” function in DAX. The “Search” function also support wildcards.

So if you want sales for every products that contains the word “Mountain” you can write a query like this.

EVALUATE
FILTER (
    CALCULATETABLE (
        ADDCOLUMNS (
            VALUES ( 'Product'[Product Name] ),
            "Sum of Sales Amount", 'Internet Sales'[Internet Total Sales]
        )
    ),
    'Internet Sales'[Internet Total Sales] <> 0
        && SEARCH ( "*Mountain*", 'Product'[Product Name], 1, 0 ) <> 0
)

And the result will look like this

Search 1

Below is a more complex sample. It returns all products that contains the word “Mountain”. But it also returns the percentage of the total for all products containing the word “mountain”

DEFINE
    MEASURE 'Internet Sales'[Total] =
        CALCULATE (
            'Internet Sales'[Internet Total Sales],
            FILTER (
                ALL ( 'Product'[Product Name] ),
                SEARCH ( "*Mountain*", 'Product'[Product Name], 1, 0 ) <> 0
            )
        )
EVALUATE
FILTER (
    CALCULATETABLE (
        ADDCOLUMNS (
            VALUES ( 'Product'[Product Name] ),
            "Sum of Sales Amount", 'Internet Sales'[Internet Total Sales],
            "Percent of all mountain", FORMAT (
                DIVIDE (
                    'Internet Sales'[Internet Total Sales],
                    'Internet Sales'[Total]
                ),
                "Percent"
            )
        )
    ),
    'Internet Sales'[Internet Total Sales] <> 0
        && SEARCH ( "*Mountain*", 'Product'[Product Name], 1, 0 ) <> 0
)

And the results look like this

Search 2

Testing row level security in Tabular (DAX)

In this post I will demonstrate how to add row level security in your tabular model. This can be a useful feature if you want to restrict access to different dimension members for different users.

First of all we need to create a new windows user on your PC / server. Lets call this user “TestUser2”

Then I will create a new table with user names and which customers they should be able of viewing.


use [AdventureWorksDW2012]

create table [UserAccess] 
(
	FullDomainUserName varchar(200),
	DomainUser varchar (100),
	CustomerKey int
)

insert into [UserAccess] values ('yourdomain\TestUser2', 'TestUser2', 11511)
insert into [UserAccess] values ('yourdomain\TestUser2', 'TestUser2', 11512)

Open the Tabular model project in Visual Studio Data Tools.

Lets add this table to the model and then create a relationship to Customer and column CustomerKey. Click “Hide from client tools” on the UserAccess table.

After you have done this your model will look like this

Screenshot useraccess

Add a new role (Model -> Roles) called TestRole. Give this read permissions.

Then add this DAX to the DAX filter on the Customer table

='Customer'[CustomerKey]=LOOKUPVALUE('UserAccess'[CustomerKey], 'UserAccess'[FullDomainUserName], USERNAME(), 'UserAccess'[CustomerKey], 'Customer'[CustomerKey]) 

Role 1

Add your new Windows users to members

Role 2

Deploy your model.

Open a Excel pivot. When Connection to the SSAS server impersonate the TestUser2 user. After doing that your pivot will look like this.

Role pivot

Percent of parent in hierarchy (DAX)

Getting percent of parent in a dimension without a hierarchy is quite simple. But to get percent of parent in a hierarchy with several levels you will have to add some more code. In the current version of DAX you will have to write code for each level.

My sample query will only work for the hierarchy in the “Geography” dimension called “Geography”.

Geography 1

Then I will add 2 calculated measures to my model.

ParentSalesEmployee :=
IF (
    ISFILTERED ( 'Geography'[City] );
    CALCULATE (
        SUM ( [Order Quantity] );
        ALL ( 'Geography'[City] )
    );
    IF (
        ISFILTERED ( 'Geography'[State Province Name] );
        CALCULATE (
            SUM ( [Order Quantity] );
            ALL ( 'Geography'[State Province Name] )
        );
        CALCULATE (
            SUM ( [Order Quantity] );
            ALL ( 'Geography'[Country Region Name] )
        )
    )
)

And then

Ratio To Parent:=Sum([Order Quantity]) / [ParentSalesEmployee]

And the result will looks like this:

Geography 2

I hope hierachy handling will become better in next version of DAX…

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

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?