Tennick A. Practical PowerPivot & DAX Formulas for Excel 2010
Подождите немного. Документ загружается.
404 Practical PowerPivot & DAX Formulas for Excel 2010
Adding Another Dimension
select {[Date].[Calendar].[Calendar Year].members} on columns,
{[Product].[Product Categories].[Category].members} on rows
from [Adventure Works]
This query sees the addition of a second non-measure dimension (Product). Once again,
it doesn’t look satisfactory as a PowerPivot table. The resulting table is shown in Figure B-5.
Adding Another Dimension Rewritten
to Give Better Results
select {[Measures].[Reseller Sales Amount]} on columns,
{crossjoin( {[Date].[Calendar].[Calendar Year].members},
{[Product].[Product Categories].[Category].members} )} on rows
from [Adventure Works]
Here we have the same query but rewritten to produce sensible results for PowerPivot.
When you have more than one non-measure dimension, it’s a good idea to place them
both on the Rows axis and only have a measure or measures on the Columns axis. To
place two dimensions on the same axis, you can use the MDX Crossjoin() function
as here. The resulting table is shown in Figure B-6. You may see an alternative syntax
that uses the asterisk (*) or multiplication symbol, instead of explicitly using the
Crossjoin() function. Indeed, this is the syntax generated by the graphical query
designer.
Crossjoin() Query
Select {[Measures].[Internet Sales Amount],
[Measures].[Reseller Sales Amount]} on columns,
{crossjoin( {[Date].[Calendar].[Calendar Year].members},
{[Date].[Month of Year].[Month of Year].members} )} on rows
from [Adventure Works]
Figure B-5 PowerPivot table
Appendix B: MDX Queries for PowerPivot 405
You can even have different attributes from the same dimension in a Crossjoin().
There is one proviso; they must be from different hierarchies in the dimension—here
the Calendar (for SSAS veterans, it’s a user hierarchy) and the Month of Year (this is an
attribute hierarchy) hierarchies are used. The resulting table is shown in Figure B-7.
More Complex Crossjoin() Query
select
{crossjoin( {[Sales Territory].[Sales Territory].[Country].members},
{crossjoin( {[Product].[Product Categories].[Category].members},
{[Measures].[Internet Order Count],[Measures].[Reseller Order Count]} )} )}
on columns,
{crossjoin( {[Date].[Calendar].[Calendar Year].members},
{[Date].[Month of Year].[Month of Year].members} )} on rows
from [Adventure Works]
Figure B-6 PowerPivot table
Figure B-7 PowerPivot table
406 Practical PowerPivot & DAX Formulas for Excel 2010
This example is getting more sophisticated (and complex). It involves three dimensions,
four dimension hierarchies, and two measures. The resulting table is shown in Figure B-8.
More Complex Crossjoin() Query
Rewritten to Give Better Results
Select {[Measures].[Internet Order Count],
[Measures].[Reseller Order Count]}
on columns,
{crossjoin({[Sales Territory].[Sales Territory].[Country].members},
{crossjoin({[Product].[Product Categories].[Category].members},
{crossjoin({[Date].[Calendar].[Calendar Year].members},
{[Date].[Month of Year].[Month of Year].members})} )} )}
on rows
from [Adventure Works]
Hopefully, this is a big improvement. You may have to scroll to see measures with
values. Please note that only measures appear on the Columns axis. The Rows axis has a
Crossjoin() within a Crossjoin() within a Crossjoin(). The resulting table is
shown in Figure B-9.
Figure B-8 PowerPivot table
Figure B-9 PowerPivot table
Appendix B: MDX Queries for PowerPivot 407
A Navigation Query
select {[Measures].[Internet Sales Amount]} on columns,
{descendants([Customer].[Customer Geography].[Country].[France],
[Customer].[Customer Geography].[City])}
on rows
from [Adventure Works]
The navigation functions within MDX are very useful. PowerPivot has converted a
Descendants() self to a Descendants() self_and_before when flattening
the result set. Here’s an example using Descendants(). The resulting table is shown in
Figure B-10.
A Range Query
select {[Date].[Calendar].[Calendar Quarter].[Q1 CY 2003]:
[Date].[Calendar].[Calendar Quarter].[Q3 CY 2003]} on columns,
{[Measures].[Reseller Sales Amount]} on rows
from [Adventure Works]
This time, it’s a range query with the colon (:) operator. The results may not be to
your liking. The resulting table is shown in Figure B-11. Please note that the range
operator requires that both members belong to the same attribute hierarchy or to the
same level of a user hierarchy.
Figure B-10 PowerPivot table
Figure B-11 PowerPivot table
408 Practical PowerPivot & DAX Formulas for Excel 2010
A Range Query Rewritten to Give Better Results
select {[Measures].[Reseller Sales Amount]} on columns,
{[Date].[Calendar].[Calendar Quarter].[Q1 CY 2003]:
[Date].[Calendar].[Calendar Quarter].[Q3 CY 2003]} on rows
from [Adventure Works]
Let’s have another go at the range query. This should look nicer. If you are familiar
with MDX, PowerPivot adds a lovely twist. It also brings back the user hierarchy above
the quarters as it flattens the result set—you won’t see this in SSMS. The resulting table
is shown in Figure B-12.
Attribute or User Hierarchies?
select {[Measures].[Reseller Sales Amount]} on columns,
{[Date].[Calendar Quarter].[Calendar Quarter].[Q1 CY 2003]:
[Date].[Calendar Quarter].[Calendar Quarter].[Q3 CY 2003]} on rows
from [Adventure Works]
The previous query returned the user hierarchy above the quarter. If you don’t want
the hierarchy, then you can use an attribute hierarchy, as here. The resulting table is
shown in Figure B-13.
Figure B-12 PowerPivot table
Figure B-13 PowerPivot table
Appendix B: MDX Queries for PowerPivot 409
Sorting Results on a User Hierarchy
Select {[Measures].[Internet Sales Amount]} on columns,
{order({[Product].[Product Categories].[Subcategory].members},
[Measures].[Internet Sales Amount],bdesc)} on rows
from [Adventure Works]
This is a classic sort that breaks the Product Categories hierarchy—the subcategories
are sorted regardless of the category to which they belong. Note that it returns the
hierarchy above the subcategory. The resulting table is shown in Figure B-14.
Sorting Results on an Attribute Hierarchy
select {[Measures].[Internet Sales Amount]} on columns,
{order( {[Product].[Subcategory].[Subcategory].members},
[Measures].[Internet Sales Amount],bdesc)} on rows
from [Adventure Works]
If you don’t want to return the hierarchy above the subcategory, use an attribute
hierarchy, rather than a user hierarchy, as there’s no user hierarchy to break, then you
can use desc rather than bdesc for the Order() function. The resulting table is
shown in Figure B-15.
Figure B-14 PowerPivot table
Figure B-15 PowerPivot table
410 Practical PowerPivot & DAX Formulas for Excel 2010
Filtering Results
select {[Measures].[Internet Sales Amount],
[Measures].[Reseller Sales Amount]} on columns,
{filter({[Product].[Product Categories].[Category].members},
[Measures].[Internet Sales Amount] > 750000 or
[Measures].[Internet Sales Amount] < 500000)} on rows
from [Adventure Works]
The Filter() function in MDX operates like a Where clause in SQL. The
resulting table is shown in Figure B-16.
A Calculated Measure
with member [Measures].[Customer Sales] as
[Measures].[Internet Sales Amount]
member [Measures].[Retailer Sales] as
[Measures].[Reseller Sales Amount]
member [Measures].[Total Sales] as
[Measures].[Internet Sales Amount] + [Measures].[Reseller Sales Amount]
select {[Measures].[Customer Sales],[Measures].[Retailer Sales],
[Measures].[Total Sales]} on columns,
{[Date].[Calendar].[Calendar Year].members} on rows
from [Adventure Works]
Here you are extending the cube by adding an ad-hoc measure on the fly. Please
remember that a lot of this work can be done graphically—do you recall the Calculated
Member Builder dialog from much earlier in the book? The resulting table is shown in
Figure B-17.
Figure B-16 PowerPivot table
Figure B-17 PowerPivot table
Appendix B: MDX Queries for PowerPivot 411
KPI Query
with member [Measures].[Actual] as
KPIValue("Product Gross Profit Margin"),format_string = "Percent"
member [Measures].[Target] as
KPIGoal("Product Gross Profit Margin"),format_string="Percent"
member [Measures].[Status] as
KPIStatus("Product Gross Profit Margin")
member [Measures].[Trend] as
KPITrend("Product Gross Profit Margin")
select {crossjoin({[Date].[Fiscal].[Fiscal Year].[FY 2003],
[Date].[Fiscal].[Fiscal Year].[FY 2004]},{[Measures].[Actual],
[Measures].[Target],[Measures].[Status],[Measures].[Trend]})}
on columns,
{[Product].[Product Categories].[Category].members} on rows
from [Adventure Works]
This is how you pull key performance indicators (KPIs) out of the SSAS cube. It’s
valid MDX but doesn’t look that good in a PowerPivot table. It’s the same problem as
before, with the non-measure dimensions on the Columns axis. The resulting table is
shown in Figure B-18.
KPI Query Rewritten to Give Better Results
with member [Measures].[Actual] as
KPIValue("Product Gross Profit Margin"),format_string = "Percent"
member [Measures].[Target] as
KPIGoal("Product Gross Profit Margin"),format_string="Percent"
member [Measures].[Status] as
KPIStatus("Product Gross Profit Margin")
member [Measures].[Trend] as
KPITrend("Product Gross Profit Margin")
select {[Measures].[Actual],
[Measures].[Target],[Measures].[Status],[Measures].[Trend]}
Figure B-18 PowerPivot table
412 Practical PowerPivot & DAX Formulas for Excel 2010
on columns,
{crossjoin({[Date].[Fiscal].[Fiscal Year].[FY 2003],
[Date].[Fiscal].[Fiscal Year].[FY 2004]},
{[Product].[Product Categories].[Category].members})}
on rows
from [Adventure Works]
This is our last query in this appendix. It fixes the previous query. The resulting table
is shown in Figure B-19.
MDX is quite a powerful language! We have seen only a few queries in this appendix.
However, there may be just enough to get you started writing your own MDX queries.
Figure B-19 PowerPivot table
DMX Queries
for PowerPivot
Appendix C