다음을 통해 공유

DAX: Returning the Nth Selected Value in a Slicer

  • 아티클

by Paul Turley, Mentor, SolidQ

A client came to me with a challenge this week that, although simple in concept, posed an interesting challenge that I hadn’t solved before using a tabular model and DAX.  She had cost information for several hospitals in a PowerPivot model.  Using an Excel pivot table published to a SharePoint site, users were to select any combination of hospitals using a slicer and compare relative costs for these facilities and other measure values.  After working on the problem and have two working solutions.  I don’t think one is necessarily better or worse than the other but each have advantages over the other.  Thanks to Rob Collie and Marco Russo for their contributions as we’ve discussed potential solutions.

In production, the model has multiple tables and a lot of cost information but the essence of the problem is represented in this sample:

http://sqlserverbiblog.files.wordpress.com/2013/07/image.png

A fact table contains cost information and a HospitalID key, which is related to a lookup table of hospitals.  Now the challenge is to allow users to select two different hospitals and return the name and related measure values for the selected hospitals so we can perform comparisons on the same row.  Later, in production, users will select 3 and maybe up to 6 hospitals for comparison.  My example only shows two.  My first thought was to just add the hospitals to rows in the PivotTable before the measures but they have some specific statistical calculations to perform with a group of selected hospitals that can't be done if these are rows in a PivotTable.

Solution #1: One slicer per selection

I added two copies of the Hospital table to the model without any relationships.  These are “disconnected tables” meaning that including these in queries will have no effect on filtering data in other tables unless used explicitly in calculation.  They will be used to drive slicers and filters, and used in special calculations.

http://sqlserverbiblog.files.wordpress.com/2013/07/image1.png

In an Excel PivotTable or Power View report, a slicer is created based on each of these two tables.

Calculated measures are added to the fact table to calculate and filter the existing  Total Cost measure.  Of course, similar calculated measures could be created to calculate any other measure as well.

Selected 1 Total Cost:=CALCULATE([Total Cost], FILTER(Cost, [HospID]=MIN(SelectedHospital1[HospID])))

Selected 2 Total Cost:=CALCULATE([Total Cost], FILTER(Cost, [HospID]=MIN(SelectedHospital2[HospID])))

I used the MIN function only to make the expression syntactically correct and because the HospID field in the fact table can only be compared to a single value in the lookup table.  Functionally, this doesn’t really do anything if the user to chooses one value in the first slicer.  If they happen to select more than one item, this will use the one with the smallest HospID value.  This technique works fine with numeric values but aggregate functions can’t be used with text type fields.  To return the selected hospital name in another calculation, I used the FIRSTNONBLANK function.  Notice the required second argument, normally used to filter values in a field.  In this case, I just used an expression that always evaluates to True:

Selected 1 Name:=FIRSTNONBLANK(SelectedHospital1[HospName],1=1)

Selected 2 Name:=FIRSTNONBLANK(SelectedHospital2[HospName],1=1)

A simple calculation is used to calculate the difference between the first selected item cost and the second selected item cost:

Selected Diff Total Cost:=[Selected 1 Total Cost]-[Selected 2 Total Cost]

In an Excel PivotTable, I add a slicer for each of the two lookup tables and the calculated measures for the values.  The final product is easy to use.  We select any hospital from the first slicer and any hospital from the second slicer to compare costs between them:

http://sqlserverbiblog.files.wordpress.com/2013/07/image2.png

Solution #2: One slicer to select multiple items

This solution is a little more elegant in my opinion.  I need to give credit to Marco Russo for working out the DAX calculations for this.  He has a gift for looking at a problem differently and finding a unique solution.  Rather than using one table for each selected facility, one table is used to drive a multi-select slicer:

http://sqlserverbiblog.files.wordpress.com/2013/07/image3.png

If this were a multidimensional model, the problem could be addressed in MDX by using the ITEM property for a set of members.  For example, to return the second selected hospital in a calculated member expression, an expression like this could be used: [Selected Hospital].[Hospital Name].ITEM(1)

…but DAX doesn’t work that way.  We think in terms of Rows and not Sets and there is no Items collection.

To get the first selected hospital, we can use the TOPN function to get the first item in the set of rows returned by this table.  This expression calculates the total cost for that item:

Selected A Cost:=
CALCULATE([Total Cost], FILTER('Cost', [HospID]=
TOPN( 1, VALUES( SelectedHospitals[HospID] ), SelectedHospitals[HospID], 1 )
)
)

The TOPN function needs 4 arguments: the first tells it how many rows to return (in our case only 1… the first row), the second is a table (the VALUES function turns a bunch of field values into a table), the third is the Order By expression, and the fourth argument specifies descending or ascending sort order.

To return the second selected item…

Since DAX has no way to just go get a specific row by row number or ordinal position, now we have to get really creative.  Marco’s solution is to use two different TOPN expressions.  The inner expression gets the first two rows and returns the results in descending order, using the expression: TOPN( 2, VALUES( SelectedHospitals[HospID]), SelectedHospitals[HospID], 1 ). The outer TOPN expression just returns the first row from that result:

Selected B Cost:=
CALCULATE([Total Cost], FILTER('Cost', [HospID]=
TOPN( 1,
TOPN( 2, VALUES( SelectedHospitals[HospID]), SelectedHospitals[HospID], 1 )
, SelectedHospitals[HospID], 0 )
)
)

…to get more selected rows, we just change the number of rows returned by the inner TOPN expression:

Selected C Cost:=
CALCULATE([Total Cost], FILTER('Cost', [HospID]=
TOPN( 1,
TOPN( 3, VALUES( SelectedHospitals[HospID]), SelectedHospitals[HospID], 1 )
, SelectedHospitals[HospID], 0 )
)
)

The final solution uses a single slicer that is used to select multiple item (three in this example):

http://sqlserverbiblog.files.wordpress.com/2013/07/image4.png