![]() ![]() Here are the formulas on the worksheet at this point: In this case I want a set containing the two ‘combination’ CubeMember() functions from B7 and B12. The CubeSet() function is also able to build sets using cell references to cells containin CubeMember() functions. You’ll notice that the ‘combination’ cells only show the month names, not the day/month combinations – this is just a feature of the CubeMember() function and can be a bit misleading, but rest assured they do return the combinations you need. Cells B7 and B12 contain CubeMember() functions that return the combinations we want to filter by: Mondays in July and Wednesdays in September respectively. Here it is with the formulas visible:Ĭells B5 and B10 contain references to days of the week cells B6 and B11 contain references to months. It’s probably easier to explain this by showing a worksheet that contains six cells with CubeMember() functions in. These combinations can be built in several ways, one of which is by using cell references to other cells that themselves contain CubeMember() functions. The first thing to point out is that the CubeMember() function does not have to just return a member, it can return a combination of members (in MDX this is known as a tuple). Step 1: Build Your Combinations Using CubeMember() Lukcily it is possible to build the report you need using the Cube Functions! Here’s how: Using the Fields, Items and Sets functionality you could filter the data to only show the day/month combinations you need for each year, but since you can’t put a named set into the Filter area of a PivotTable you would have to use Excel formulas to sum up the combinations to get the totals you need: Now, imagine that you want a report with the Sales Amount measure on columns and Years on rows, and you want to filter the data so that you only see values for Mondays in July or Wednesdays in September. Here’s what the data looks like in a PivotTable: ![]() There’s a Date table with dates, months and years in, and an Internet Sales table with sales data in and a measure called Sales Amount. Imagine you’ve got a very simple Power Pivot model that looks like this: ![]() No knowledge of MDX or DAX is needed but if you’re new to the Excel Cube Functions I recommend that you watch this video of a presentation by Peter Myers, which provides an excellent introduction to them. This post describes how they can be used to build a report with a complex OR filter using data from Power Pivot (it’s equally applicable to SSAS) that shows a number of advanced uses of these functions. The Excel Cube Functions are incredibly powerful, and I’m still amazed at the kind of problems they can solve. ![]()
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |