DAX

I know it’s been ages since I posted something, and I do have lots of lame excuses so maybe it deserves a post on it’s own. But for now, I want to jot down a workaround on how to rank a column based on the slicer selections for the same column.

The other day, I was working on an excel dashboard and I got a requirement to rank a field while the field values are there on the slicer. While I am used to getting requests on ranking a field based on a measure, this was the first time someone was asking me to rank a field based on a measure while the column was on the slicer. The user just wanted to rank within the selected values of the field in the slicer and not as a whole. To explain this, I just made a very simple example with the model given below

The FactLicense table contains data on new liquor licenses and I made a simple measure License Count to count the number of licenses. The FactLicense table is related to the Geo table through the Zip Code column. The requirements were that

• the City field should be ranked by License Count
• there should be a slicer displaying the values for City
• the ranks should update based on the Slicer selections

Solution

1) To solve the first part of the requirement, I made a simple ranking measure by City as shown below.

LC Ranked by City:=RANKX(ALL(Geo[City]), [License Count])

You can see the results in the pivot table below.

2) For the second requirement, we can just add a slicer for the City field and then connect it to the Pivot Table.

3) Now the third requirement is the interesting one. When I try to filter by some cities, I get the result below.

You can see that the Ranks don’t get updated based on the slicer selections. The user wants the ranks to start from 1 but you can see that the ranks are still based on the entire city list and not just the selected city list. Now it is impossible (atleast as far as I know) to reset the ranking if the same field is on the rows as well as the slicer. The reason is that the Rank measure operates on ALL(Geo[City]) and hence removes any filter or selection that is already made. So what do we do?

4) A simple workaround is to make a calculated column (say City Slicer) and make the Slicer based on this new calculated column.

Make sure to remove the old slicer as well as connect this new slicer to the pivot table.

5) Now when we select the City in the slicer, you can see that the Ranks get reset.

You can further rename the Slicer such that it says only City and you can hide this calculated column so that it is not visible to the other users analyzing the model. This will help avoiding confusion among the end users (Eg – why do we have 2 city fields?) and at the same time, you can make your Excel dashboard with the fulfilled requirements.

Update

Within seconds of posting, Miguel Escobar (twitter) comes up with a much better way of doing this using the ALLSELECTED().

LC Ranked by City1:=RANKX(ALLSELECTED(Geo[City]), [License Count])

This would be the ideal way to do it as we don’t have to create a new calculated column and still achieve the same results. Thanks a lot for this Miguel!

Recently, I got a question from one of my readers on whether there is a way to split a single delimited row into multiple rows using DAX. I had kind of demonstrated the opposite to Group Multiple Rows to Single Delimited Row in PowerPivot here and this was another interesting challenge. Read on for the solution.

Business Requirement

Suppose there is a table which has two columns – StudentName and Subject.

The end result should be

Solution

We will be using DAX queries to generate a table that will have the desired output. For this demonstration, I will be creating a PowerPivot model and running DAX queries in DAX Studio on top of the Excel PowerPivot model.

1) Create a linked table for the Student.

2) Create a table called DummyTbl which is just a list of running numbers from 1 till n. The value of n should be equal to the max number of subjects that a student can have. In this demo, I am assuming that a student can have a max of 10 subjects, so my Dummy table will consist of numbers 1 – 10.

3) Now let us create the DAX queries in DAX Studio. First, we will create a computed column called SubCnt which will give the number of subjects for each row.

EVALUATE
SUMMARIZE (
Student,
[StudentName],
[Subject],
“SubCnt”,
1 + Len ( [Subject] ) - Len ( Substitute ( [Subject], “,”, “” ) )
)

4) The only way we can increase the number of rows of a table is by using the function CrossJoin. So let us CrossJoin the Student table with the DummyTbl.

EVALUATE
Crossjoin (
SUMMARIZE (
Student,
[StudentName],
[Subject],
“SubCnt”,
1 + Len ( [Subject] ) - Len ( Substitute ( [Subject], “,”, “” ) )
),
DummyTbl
)

5) Now filter the resultant table where SubCnt is less than or equal to Dummy column. Now we have got the exact number of rows needed for the end result.

EVALUATE
Filter (
Crossjoin (
SUMMARIZE (
Student,
[StudentName],
[Subject],
“SubCnt”,
1 + Len ( [Subject] ) - Len ( Substitute ( [Subject], “,”, “” ) )
),
DummyTbl
),
DummyTbl[Dummy] <= [SubCnt]
)

6) The only thing left is to split the delimited subjects to the single subject for each row. And the only way to split delimited values is to use the PATHITEM function. To use the PATHITEM function, we should substitute the commas (,) with the pipe (|) symbol.

EVALUATE
ADDCOLUMNS (
FILTER (
CROSSJOIN (
SUMMARIZE (
Student,
[StudentName],
[Subject],
“SubCnt”,
1 + Len ( [Subject] ) - Len ( Substitute ( [Subject], “,”, “” ) )
),
DummyTbl
),
DummyTbl[Dummy] <= [SubCnt]
),
“SubName”,
PATHITEM (
SUBSTITUTE ( Student[Subject], “,”, “|” ),
DummyTbl[Dummy]
)
)

7) Now all we need to do is to select the two required columns.

EVALUATE
SUMMARIZE (
ADDCOLUMNS (
FILTER (
CROSSJOIN (
SUMMARIZE (
Student,
[StudentName],
[Subject],
“SubCnt”,
1 + Len ( [Subject] ) - Len ( Substitute ( [Subject], “,”, “” ) )
),
DummyTbl
),
DummyTbl[Dummy] <= [SubCnt]
),
“SubName”,
pathitem (
Substitute ( Student[Subject], “,”, “|” ),
DummyTbl[Dummy]
)
),
[StudentName],
[SubName]
)
ORDER BY [StudentName]

Hopefully this post will help you when you encounter a similar situation!

In one of my previous posts - Row Selection Using Slicers in PowerPivot - Part 1, I had demonstrated the use of what Marco Russo defined as Reverse Linked Tables and Linkback tables. A particularly eye-catching thing in my post was the use of Microsoft Query instead of DAX Query to obtain the required reverse-linked table and one of the reasons I gave was that it was difficult to do an UNION operation using DAX queries (yes, you heard it right. I said difficult and not impossible). Well, since difficult is such a subjective word, I decided to jot down the technique, maybe it might seem easy for you guys!

For the purpose of this demonstration, I am using two tables having identical structures. There are just two columns in the tables - country and state and we need to do an UNION operation on these two tables.

The result should be 7 rows as shown below

Follow the steps below for the solution:-

1) Import the two tables to PowerPivot (you can also use this technique on a SSAS tabular model). I will be using DAX Studio for writing my queries and displaying the results (though you might as well as use this in SSMS or in the DMX query editor for SSRS depending on your purpose).

2) The first thing to understand here is that DAX, as a query language, can not add rows usually, and UNION requires that we get more rows (since it is the combined result of both the tables). However, there is an operator in DAX which generally generates more rows than its source tables – CROSSJOIN (except when any one of the participating tables has only one row). So let’s first crossjoin the two tables and see the results.

Now you can see that we get 12 rows, however no single column gives the result that we need. Somehow, we need to get a logic to filter the 5 rows and also a logic to combine the right results in one calculated column.

3) To identify the individual rows, let us add a rank column to each of the two tables and then crossjoin them.

EVALUATE
CROSSJOIN (
ADDCOLUMNS (Table1,”Rnk1″,RANKX ( Table1, Table1[State],, 1, DENSE ) ),
ADDCOLUMNS (Table2,”Rnk2″,RANKX ( Table2, Table2[State],, 1, DENSE ) )
)

4) When I looked at this resultset initially, I felt that I could just filter all rows having Rank1 = 1 and Rank2 = 2 and then add a calculated column each for Country and State such that if Rank1 = 1, then the value comes from Table2 else it comes from Table1.

EVALUATE
ADDCOLUMNS (
FILTER (
CROSSJOIN (
ADDCOLUMNS (Table1,”Rnk1″,RANKX ( Table1, Table1[State],, 1, DENSE ) ),
ADDCOLUMNS (Table2,”Rnk2″,RANKX ( Table2, Table2[State],, 1, DENSE ) )
),
[Rnk1] = 1 || [Rnk2] = 2 ),
“UnionCntry”, IF ( [Rnk1] = 1, Table2[Country], Table1[Country] ),
“UnionState”, IF ( [Rnk1] = 1, Table2[State], Table1[State] )
)

Even though the UnionCntry and UnionState columns might appear right, they are not, as one row is missing (in this case, the row with IN country and KL state). This happens because there will always be one row which has Rnk1 = 1 and Rnk2 = 2, and hence there is an overlap. So we need to think of a different technique to filter the rows.

5) Since we are short of one row, we need to include one more extra row having the complementary values of ranks for the overlapping row (in this case, the row with Rnk1=2 and Rnk2=1). This can be done by using the query given below.

EVALUATE
FILTER(
CROSSJOIN (
ADDCOLUMNS (Table1,”Rnk1″,RANKX ( Table1, Table1[State],, 1, DENSE ) ),
ADDCOLUMNS (Table2,”Rnk2″,RANKX ( Table2, Table2[State],, 1, DENSE ) )
), ([Rnk1]=1 || [Rnk2]=2) || ([Rnk1]=2 && [Rnk2]=1))

6) Now all we have to do is to get the logic for creating the calculated columns. We can do it with the help of a SWITCH statement by (a) assigning the extra row (Rnk1=2 and Rnk2=1) to Table1 and (b) making sure that the overlapping row (Rnk1=1 and Rnk2=2) is assigned to Table2. This can be done by the following query

EVALUATE
ADDCOLUMNS(
FILTER(
CROSSJOIN (
ADDCOLUMNS (Table1,”Rnk1″,RANKX ( Table1, Table1[State],, 1, DENSE ) ),
ADDCOLUMNS (Table2,”Rnk2″,RANKX ( Table2, Table2[State],, 1, DENSE ) )
), ([Rnk1]=1 || [Rnk2]=2) || ([Rnk1]=2 && [Rnk2]=1))
, “UnionCountry”, SWITCH(TRUE,
([Rnk2]=2 && [Rnk1]<>1) || ([Rnk2]=1 && [Rnk1]=1)
,Table1[Country], Table2[Country])
, “UnionState”, SWITCH(TRUE,
([Rnk2]=2 && [Rnk1]<>1) || ([Rnk2]=1 && [Rnk1]=1)
,Table1[State], Table2[State]))

Now you can see that the UnionCountry and UnionState column gives us the required results for a UNION ALL operation. This approach can be easily extended in case we have more than two tables to join. The only scenario where this approach will not work is when any of the participating tables has just one row (as the cross join will not give us more rows. Eg, cross-joining two tables with 3 and 1 rows will give only 3 rows whereas we need 4 rows for the UNION ALL). However, this can be resolved by adding a temporary table with two rows (so the cross join will be 3 * 1 * 2 = 6 rows) and then using this logic.

7) To do the UNION operation instead of UNION ALL, we should remove all duplicate rows. For that, we can use SUMMARIZE function.

EVALUATE
SUMMARIZE(
ADDCOLUMNS(
FILTER(
CROSSJOIN (
ADDCOLUMNS (Table1,”Rnk1″,RANKX ( Table1, Table1[State],, 1, DENSE ) ),
ADDCOLUMNS (Table2,”Rnk2″,RANKX ( Table2, Table2[State],, 1, DENSE ) )
), ([Rnk1]=1 || [Rnk2]=2) || ([Rnk1]=2 && [Rnk2]=1))
, “UnionCountry”, SWITCH(TRUE,
([Rnk2]=2 && [Rnk1]<>1) || ([Rnk2]=1 && [Rnk1]=1),
Table1[Country], Table2[Country])
, “UnionState”, SWITCH(TRUE,
([Rnk2]=2 && [Rnk1]<>1) || ([Rnk2]=1 && [Rnk1]=1),
Table1[State], Table2[State]))
, [UnionCountry], [UnionState])

In this scenario, there is no difference in the result as there are no duplicate rows. Let me know what you think of this!

Update - 11/20/2015

I still see some hits on this page, so just wanted the readers to be aware that DAX now supports the UNION function (along with other set based function) out of the box

UNION Function DAX

This post is a continuation of the Row Selection Using Slicers in PowerPivot – Part 1 (so it might be a good idea to read that post before this). In our last post, we discussed the basic solution pattern for row selection using slicers in PowerPivot. In this post, we will discuss two other scenarios which are slightly more complex but still uses the same pattern. Scenario III - GroupNames in multiple dimensions
Our data model for this scenario will have a Product table, a Customer table and a Fact table which shows the sales by Product and Customer. The requirement is to select either Product or Customer in the slicer and display the appropriate rows in the pivot table. 1) As per the solution steps, we first have to create the Group table Note that we need two grain columns here, as the GroupNames belong to two dimensions – Product and Customer. The GroupName will be Customer and Product, and the GroupValues will be the actual product or customer values depending on the GroupName. As with the previous posts, I use MS Query to generate this table.  

SELECT Expr1000 AS GroupName, Customer1 AS GroupValue, ProductKey1 AS Product, Customer FROM
(
SELECT ‘Customer’ , C.Customer1, `Sheet1$`.ProductKey1, `Sheet1$`.Customer
FROM `Sheet1$` `Sheet1$`
INNER JOIN `Sheet1$` C
ON `Sheet1$`.Customer=C.CustomerKey
UNION ALL
SELECT ‘Product’, P.Product, `Sheet1$`.ProductKey1 , `Sheet1$`.Customer
FROM `Sheet1$` `Sheet1$`
INNER JOIN `Sheet1$` P
ON `Sheet1$`.ProductKey1=P.ProductKey
)

2) Add this new table (Group) to the data model and then relate the Product & Customer column in this table to the ProductKey column  in the Product table & CustomerKey column in the Customer table. 3) Hide the original measure Sales, and then create a new measure with the formula given below

NewSales:=CALCULATE(SUM(Fact[Sales]), ‘Group’)

4) Check out the results by creating a pivot table with GroupValue on the rows, NewSales on Values and GroupName in Slicers. Download the Excel 2013 file with the solution from here. Scenario IV – Hybrid Scenario 
In this scenario, we are going to combine all the different scenarios we discussed till now and see how to handle that. The data model for this scenario will contain a Fact table which shows sales by Product and Customer, a Product table with the Product and SubCategory information, a Customer table and a Promotions table which has a many to many relationship with Product. The requirement is to have SubCategory (Scanario I), Promotions (Scenario II) and Customer (Scenario III) on slicers. 1) As usual, the first step is to generate the Group table. Note that even though we have only 4 subcategories, we end up with 6 rows because we will have to ensure that the grain is at a Product and Customer level. Similarly, we end up with  9 rows for Promotions when we convert the granularity of the Promotions table from just Product to the Product and Customer level. Find below the MS Query way of generating this table

SELECT Expr1000 AS GroupName, SubCategory AS GroupValue, ProductKey1 AS Product, Customer FROM
(
SELECT ‘SubCategory’,S.SubCategory, `Sheet1$`.ProductKey1, `Sheet1$`.Customer
FROM `C:UsersJasonDesktopBlogRowSelectionScenario4_Hybrid.xlsx`.`Sheet1$` `Sheet1$`
INNER JOIN `C:UsersJasonDesktopBlogRowSelectionScenario4_Hybrid.xlsx`.`Sheet1$` `S`
ON `Sheet1$`.ProductKey1=S.ProductKey
UNION ALL
SELECT ‘Promotions’,P.Promotions, P.Product1, S.Customer
FROM `C:UsersJasonDesktopBlogRowSelectionScenario4_Hybrid.xlsx`.`Sheet1$` `P`
INNER JOIN `C:UsersJasonDesktopBlogRowSelectionScenario4_Hybrid.xlsx`.`Sheet1$` `S`
ON P.Product1=S.ProductKey1
UNION ALL
SELECT ‘Customer’, Customer, ProductKey1, Customer
FROM `C:UsersJasonDesktopBlogRowSelectionScenario4_Hybrid.xlsx`.`Sheet1$`
WHERE Customer IS NOT NULL
)

2) Add this new table (Group) to the data model and then relate the Product & Customer column in this table to the ProductKey column  in the Product table & CustomerKey column in the Customer table. 3) Hide the original measure Sales, and then create a new measure with the formula given below

NewSales:=CALCULATE(sum(Fact[Sales]), ‘Group’, Promotions)

Also note the use of the Promotions table (which is the many to many table) in the filter part of the calculate statement. 4) Check out the results by creating a pivot table with GroupValue on the rows, NewSales on Values and GroupName in Slicers. Download the Excel 2013 file with the solution from here. With that, we come to the end of this 2 part series on how to change the rows using slicers in PowerPivot. As you would have seen, the bulk of the work lies in generating the Group table and the rest is pretty simple. Hope these scenarios will help you in case you ever need to do the same.

A couple of months back, I had written a post on ‘Measure selection using Slicers in PowerPivot’ and this turned out to be one of my most popular ones in the PowerPivot / DAX category. It was just a matter of time before someone asked how to do the same with rows or dimension attributes using slicers in PowerPivot. And the case supporting it is also becoming strong with more and more dashboards being built in Power View and Excel. Well, as usual, this functionality is not available out of the box but you could simulate it with a little change to your data model. Read on for the solution.

I will be first briefly explain the solution pattern and then apply it to a couple of scenarios.

Solution
1) A new table should be built and added to the existing data model. The new table should have at least 3 columns
- GroupName : the text displayed in the slicers (for e.g., Product, Customer, etc.)
- GroupValue : the actual values that should be displayed in the rows on selecting the slicer values (for e.g., if Product is selected in the slicer for GroupName, then GroupValues will be the Product values)
- Grain of the Tables : If all the GroupNames displayed in the slicer belong to the same dimension (for eg, Product, Category and SubCategory in the Product dimension), then only one column is required which would be the grain of the dimension (to be precise, the column of the table linked to the facts which is usually the grain of the dimension). If multiple dimensions are involved in the GroupNames (for eg, Product and Customer), then the number of columns increases based on the grain of the participating dimensions.For each GroupName, all valid combinations of GroupValue and Grain columns should be generated.
2) The grain columns should then be related to the dimensions.
3) Create new calculated measures for each of the required measure by passing the new table in the filter part of a calculate statement and then use them in the pivot tables or charts.

Scenario I - GroupNames in one dimension and 1-to-Many Relationship
Our data model for this scenario will have a Product table and a Fact table which shows the sales by Product and Customer. The Product table has ProductKey, Product, SubCategory and Category columns. Our requirement is that we should be able to select Product, SubCategory or Category in the slicers and display the selected values on the row of the pivot table.

1) As per the solution steps, we will have to create a new table as shown below

Since the slicers need to display the three levels, the GroupName values will be Category, SubCategory and Product. The granularity is at the Product level and hence that will be the third column. Note that for each value in GroupName, there should be a row mapping the GroupValue to the grain column (which is Product here). This sort of table is very easy to create if the source is a relational database. For this demo, I used linked tables and hence to automate the creation of this table, I had to use Microsoft Query as a datasource to pull data from the linked tables.

SELECT Expr1000 AS GroupName, Category AS GroupValue, ProductKey AS Product FROM (
SELECT ‘Category’ , ‘Sheet1$’.Category , ‘Sheet1$’.ProductKey
FROM ‘Sheet1$’ ‘Sheet1$’
WHERE ‘Sheet1$’.ProductKey IS NOT NULL
UNION ALL
SELECT ‘SubCategory’ , ‘Sheet1$’.SubCategory , ‘Sheet1$’.ProductKey
FROM ‘Sheet1$’ ‘Sheet1$’
WHERE ‘Sheet1$’.ProductKey IS NOT NULL
UNION ALL
SELECT ‘Product’ , ‘Sheet1$’.Product , ‘Sheet1$’.ProductKey
FROM ‘Sheet1$’ ‘Sheet1$’
WHERE ‘Sheet1$’.ProductKey IS NOT NULL
)

(Please note that I am not a MS query datasource expert and I had to workaround getting the column alias names by bringing the outermost select statement. This should have been a simple UNION ALL in SQL Server). If there is any data change in the linked tables, the Refresh button should be clicked twice in the Excel window (so that the Query can populate the changed data in the new table as well as refresh the PowerPivot model with the changed data). 2) Add this new table (Group) to the data model and then relate the Product column in this table to the ProductKey column in the Product table.

3) Hide the original measure Sales, and then create a new measure with the formula given below

NewSales:=CALCULATE(SUM(Fact[Sales]), ‘Group’)

4) Check out the results by creating a pivot table with GroupValue on the rows, NewSales on Values and GroupName in Slicers.

Download the Excel 2013 file with the solution from here.

Scenario II - GroupNames in one dimension and Many-to-Many Relationship
Our data model for this scenario will have a Product table, Fact table which shows the sales which shows the sales by Product and Customer, and two tables – Sales Rep and Promotions which will have a many to many relationship with Product.

1) The Group table should have the below format

To create the Group table, we again use Microsoft Query as a datasource to pull data from the linked tables.

SELECT Expr1000 AS GroupName, SalesRep AS GroupValue, Product1 AS Product FROM (
SELECT ‘SalesRep’, `Sheet1$`.SalesRep, `Sheet1$`.Product1
FROM `Sheet1$` `Sheet1$`
WHERE `Sheet1$`.Product1 IS NOT NULL
UNION ALL
SELECT ‘Promotions’ , `Sheet1$`.Promotions , `Sheet1$`.Product2
FROM `Sheet1$` `Sheet1$`
WHERE `Sheet1$`.Product2 IS NOT NULL
)

2) Add this new table (Group) to the data model and then relate the Product column in this table to the ProductKey column in the Product table.

3) Hide the original measure Sales, and then create a new measure with the formula given below

NewSales:=CALCULATE(sum(Fact[Sales]), ‘Group’, ‘Promotions’, ‘Rep’)

4) Check out the results by creating a pivot table with GroupValue on the rows, NewSales on Values and GroupName in Slicers.

Download the Excel 2013 file with the solution from here.

I have got two more scenarios to show but guess that will have to wait till the second part of this series – Row Selection Using Slicers in PowerPivot – Part 2.

Note
There are some unique concepts here which might raise questions like
- why didn’t I use DAX queries to seed my Group table? Well, I couldn’t find a easy way to do union in DAX and that is the reason why I used MS query. It might not be the best way but it sure is an option.
- Is there any other way to avoid the double refresh of the file? I couldn’t find any other option to do so (actually, you will need to do that even if you are using DAX queries to seed the Group table). That is, if you are avoiding VBA. Javier Guillen has written a post on how to do it with a single refresh using VBA (check out the final part of his blog for the VBA code).