DAX: Always get the Latest date for a slicer

You’ve done it. After some development, your Power Pivot model has some awesome-sauce, has a slicer for the day/month/year, and is ready to do some serious data insight. You set your slicer to the current moment in time, deploy it to a Power Pivot gallery in SharePoint, and start using your model. But then…tomorrow happens. And now your slicer is out of date. Every time a new day happens, the slicer gets farther and farther away. Your users don’t want to click the latest date every time they want to use the model, you’d like to make it dynamic so that you don’t have to click on the latest day every time. Rob Collie does an excellent introduction into this topic in his new book, and gives a good background on using a latest date slicer. I was recently reviewing this concept with a class when they went through all the different scenarios that this might apply in. (You know who you are! :)) I thought this would be a good opportunity for a blog post, to show the different levels and ways that this concept could be applied:

 

DATE TABLES THAT GO INTO THE FUTURE, FACT TABLES THAT GO UP TO TODAY

The first scenario we might run into is that our fact table has dates of that go up to today’s date, but our date dimension pulls dates that go into the future. Facts like this usually surround performance or actually happened metrics. Consider a date dimension that goes out 10 years into the future. Do we really need to pull the maximum date of the date dimension? No, as our slicer will be unusable until that end date happens, 10 years away! A neat trick to get around this is to pull the maximum date from the fact table, thereby only pulling the most recent date from our fact. We create a calculated column in our date table, with the below syntax:

=IF(MAX(‘Internet Sales'[OrderDateKey])=[DateKey],”Latest”,FORMAT([FullDateAlternateKey],”mm-dd-yyyy”))

This gives us a slicer that looks like the below, and allows us to select “Latest” as our default value, ensuring that we always have the most recent data selected in our visualizations:

image

Now this is awesome, but I don’t know if I’d call it awesome sauce. Let’s try and modify our date slicer a little:

 

CREATING A SLICER AT THE MONTH LEVEL

Slicers are really built to be views of our data, right? Because of this, slicers should generally contain pieces of data that have less distinct values than other pieces of our model. I can modify my query above to pull the current month of data, thereby limiting my my “Latest” value slicer to only months, making it easier and less overwhelming for a user:

 

=IF(LEFT(MAX(‘Internet Sales'[OrderDateKey]),6)=LEFT([DateKey],6),”Latest”,[CalendarYear]&”-“&[EnglishMonthName])

 

I’ve highlighted my changes in red. I want to make sure I preserve the integer portion of the keys, in order to keep performance up. I’ve taken the 6 left of the max of the fact table, and joined it to all of the days that fall in the month in my date table. This way, my slicer will return all of the facts that happened in that month.

 

 

Resulting in the following slicer on screen:

 

image

 

One really quick modification I could make to this is to change number 6 in the number of characters argument of the LEFT function to 4, to return only the year. Change the last portion of the IF statement to return all of the days that fall in the current year, and I could get a “Latest” Year slicer.

 

Awesome Sauce! I could end this post here, but I wanted to present two other scenarios you might run into.

 

MAP THE LAST UPDATE FIELD IN YOUR MODEL TO THE LAST DATE OF THE SLICER

Another technique Rob has detailed over at powerpivotpro is how to use a “LastUpdated” date in your model to show the date/time you data is current as of. By changing a quick part of the statement, we can utilize that field as our “Latest” date:

=IF(INT(FORMAT(MAX(LastUpdatedDateTime[LastUpdatedDateTime]),”YYYYMMDD”))=[DateKey],”Latest”,FORMAT([FullDateAlternateKey],”mm-dd-yyyy”))

Again, I’ve highlighted my changes in red. This time, we’re pulling the LastUpdatedDateTime into our model as a datetime. In order to compare it to what our datekey is, we need to convert our date returned into a value that matches the format of our date key, which is why we have the format function included. By default, format returns the result back as text, so we wrap the whole function in an INT function to convert the result of our format as an integer.

 

DATE TABLES THAT GO INTO THE FUTURE, FACT TABLES THAT GO INTO THE FUTURE

Another common modeling issue we might see when we want to implement something like this is when BOTH the date table and the fact table go into the future, as we might commonly see in budgeting, project planning, and construction. Pulling the max date from our date table is going to give us date really far in the future, as will the fact table! A common joke I tell my students during class is “Do as I mean, not as I say”. We need to tell DAX the same thing. DAX has a built in function to return today’s date, called TODAY(). By changing the other side of our IF condition test, we can tell DAX that the record we want to tag with the “Latest” annotation is today:

=IF([DateKey]=INT(FORMAT(TODAY(),”YYYYMMDD”)),”Latest”,FORMAT([FullDateAlternateKey],”mm-dd-yyyy”))

All we did there was move our INT(FORMAT( function from the left side of the equation to the right! This breaks my earlier statement of having a lot of slicers at the day level, though, so I want to create one more to return the current month and year as my slicer value:

=IF(INT(LEFT([DateKey],6))=INT(FORMAT(TODAY(),”YYYYMM”)),”Latest”,[CalendarYear]&”-“&[EnglishMonthName])

 

The workbook is available for download here. (no size issues this time, I checked. :))

Advertisements

DAX: Date Calculation Tables

Some time ago, I blogged about using a date calculation dimension in a multidimensional cube, and how easy it makes doing time intelligence in a multidimensional model. 2014 seems to have started out as “the year of Power Pivot”, as it is definitely gaining traction with my clients and more and more of them are asking about it during engagements. One of the absolutely amazing things I get absolutely giddy about in Power Pivot is the concept of disconnected slicers. As Rob Collie best said, disconnected slicers in Power Pivot are pure magic. I particularly enjoy the “mad scientist” reference he makes. For those unfamiliar, a disconnected slicer is a piece of data that has absolutely nothing to relate to in your data, but you want to use it as a part of your analysis. For a great introduction, see Rob’s Power Pivot pro blog about it as an introduction. I was recently showing a client this mad scientist level stuff, but then took the natural progression and thought: “If I can do this to control the number of months back, can I take it a step further and create a date calculation dimension like in multidimensional too?” What, that wasn’t the first thing you thought too?

First off, let’s create some measures in Power Pivot that contain our measures and time intelligence analysis:

image

For this post, I’ve created 4: Total Sales, Calendar YTD Sales, Previous Period, and previous period diff. These are typical time intelligence functions, nothing overly special or different that I did on them. If you’re interested, these are the formulas for them:

Total Sales:=SUM([SalesAmount])

Calendar YTD:=TOTALYTD([Total Sales],’Date'[FullDateAlternateKey])

Calendar Previous Period:=CALCULATE([Total Sales],DATEADD(‘Date'[FullDateAlternateKey],-12,MONTH))

Calendar Previous Period Diff:=[Total Sales]-[Calendar Previous Period]

Not too bad, yet. Ok, let’s create a table to house our disconnected slicer. In Excel, create a table with the value you want to select and an “ID” column next to it:

image

Then, add it in to your data model, either by copying and pasting or clicking the “Add to Data Model” link in the Power Pivot ribbon. Below is my Power Pivot diagram view after importing, notice how there is no relationship between the date calculation table and the fact table:

image

Getting back in to the data view, we’ll navigate back to the fact table, and take the same approach as Rob did for his disconnected slicer to return the currently selected slicer:

Date Calculation Selected:=MIN(‘Date Calculation'[Value])

In the preceding formula, we’re taking the min value of the value column of the date calculation dimension. This allows us to then select the current active context inside of our pivot table. In the below image, I’m selecting the measure value from disconnected slicer table I created, and the Date Calculation selected measure from my fact table.

image

This is…AWESOME! However, when I go to drag on my time intelligence and performance measures, it doesn’t quite look right:

image

Whoa! That’s… not awesome. So let’s fix it. Back on the fact table, we can utilize a function called SWITCH, which works like a case statement in SQL. The syntax for switch starts with the column or value you want to evaluate, and then the cases afterwards, separated by commas. As an example, in this case, we want to swap in a different measure based on that [Date Calculation Selected] measure we created 2 paragraphs ago:

Active Measure:=SWITCH([Date Calculation Selected],1,[Total Sales],2,[Calendar YTD],3,[Calendar Previous Period],4,[Calendar Previous Period Diff])

We’re evaluating the [Date Calculation Selected], and then returning a different measure based on what the active context is for that row. Let’s go back to our pivot table, take off all of those old values and replace that didn’t look so good above and replace them with our new “Active Measure” measure:

image

Oh. My. God. That is so AWESOME!!!! Even more amazing, we could even take our date calculation table and put it in a slicer above, freeing the columns section of the pivot for something else to make this even more flexible and put in on a dashboard. This way, we can actually have the MEASURES in a slicer!

image

That is some serious awesome-sauce! The complete workbook is available for download here.

DAX: Calculating different rates on user defined inputs

A few weeks ago a client asked me an interesting question. They had a table of data, and they wanted to pass in (in their words) a variable in order to change the calculated value. I’d played around with banding before (see my post here), but I hadn’t been able to play with something quite as specific as the scenario they presented. To outline the issue, let’s say we have a user created table that consists of the different pricing tiers for our customers, similar to the below:

image

 

And we have a table of data that looks like this:

image

The challenge that the user wanted to present was to leave these 2 tables independent of each other. One challenge in DAX is that we cannot use a between statement to join tables, so we’re going to need to come up with something to give the value of the parameter we’re looking for. In the next step, I’ve now brought these 2 tables into Power Pivot, and joined in a date dimension, as shown by the below screenshot:

 

image

We can see that the data itself joins over to the date dimension fine, but we can’t join to the rates table as we are trying to go between. What we can do, however, is modify the data table to return the value of the parameter table, in a denormalized pattern. By utilizing the calculate command with a filter, we can get a list of that value repeated through our entire fact table.

 

=CALCULATE(VALUES(Rates[Value]),FILTER(Rates,Rates[Parameter]=”Hour Limit”))

In the above formula, I’m asking DAX to go to the rates table, and return back the corresponding value for the specific Hour Limit parameter. I can then repeat this same step to return the corresponding rate for each of the different tiers. Expanding on this value slightly, I can then do the same thing to return the tier 1 and tier 2 rate, to return the following values:

image

 

Now we can create a calculation to calculate the total amount that should be paid by the customer. For utility billing, it’s very common to have tiering rates, so for example, the first 2000 kWh hours are charged at one rate, but once you go over that, it’s then an additional surcharge on top of the tier 1 rate. We can come up with the amount due by using a formula like the below:

=IF([HoursUsed]<[SpecifiedLimit],([HoursUsed]*[Tier 1 Rate]),((([HoursUsed]-[SpecifiedLimit])*[Tier 2 Rate]) + ([SpecifiedLimit]*[Tier 1 Rate])))

Which gives me the following screenshot.

image

 

I can now either choose to leave it this way and hide my additional fields that are just doing the calculations, or roll them all into one. Rolling everything into one calculation so I only use one calculation in my model would result in a formula equal to the below:

 

=IF([HoursUsed]<(CALCULATE(VALUES(Rates[Value]),FILTER(Rates,Rates[Parameter]=”Hour Limit”))),
([HoursUsed]*(CALCULATE(VALUES(Rates[Value]),FILTER(Rates,Rates[Parameter]=”Tier 1 Rate”)))),
((([HoursUsed]-(CALCULATE(VALUES(Rates[Value]),FILTER(Rates,Rates[Parameter]=”Hour Limit”))))*(CALCULATE(VALUES(Rates[Value]),FILTER(Rates,Rates[Parameter]=”Tier 2 Rate”)))) +
((CALCULATE(VALUES(Rates[Value]),FILTER(Rates,Rates[Parameter]=”Hour Limit”)))* (CALCULATE(VALUES(Rates[Value]),FILTER(Rates,Rates[Parameter]=”Tier 1 Rate”))))))

 

A very handy way to get around the parameter issue in DAX is to use an excel workbook and link the Power Pivot or Tabular model to that in order to allow users to change “run-time” values, although it does require the Power Pivot model to be refreshed. This workbook can be downloaded here.

Tabular Basket Analysis: Simple Data Mining in Tabular databases

Before I get to today’s post, on a personal note I apologize for the lack of activity lately on this blog. I’ve recently moved across the country from Colorado to North Carolina, and between trying to get some things in order and my day to day work responsibilities, I didn’t have enough time in the day. I’m good now, though, so let’s get to it! 🙂

 

I was recently reading Alberto’s amazingly awesome piece on basket analysis in tabular in DAX (available here), but I had some issues implementing it on my own. I wanted to blog about this in case anyone else ran into the same issues I did when trying to implement it. At its core what the question is really asking is “Of everyone that bought Product X, who didn’t buy Product Y”? This can be a very tricky question to answer from a business intelligence perspective, but the value it gives to the business is amazingly useful. Firstly, some background. To keep this simple I am going to be using the same data set Alberto did, on Internet Sales in Adventure Works. The specific question we want to answer is: “Of everyone that purchased a mountain bike, who didn’t buy a tire accessory?” My tabular model that I use for all of my dax stuff is pretty big, but the sub-model snippet we want to initially look at looks like:

 

image 

 

As Alberto highlights, this only answers one part of the question, which is these are the products the customer actually purchased. To answer the 2nd part of the question, which is “who didn’t buy tire tubes”, we want to bring in another instance of the product dimension as a role playing dimension that we can utilize separately. There are now 2 product dimensions in our diagram, one which a relationship to the internet sales and one that has no relationship at all. If you think about it that makes sense too as the product key in the Internet Sales table wouldn’t join to the filtered products dimension as there is no associated product sold. Our new diagram looks like:

 

image

 

Let’s go into the new Product table that we just imported and rename the EnglishProductName field to Analyzed Product:

image

 

Now we can create a formula to return what was bought out of the product we want to analyze. Go to the Internet Sales table, and our formula will look like this:

Number Sales:=CALCULATE (
    COUNTROWS (DISTINCT (‘Internet Sales'[CustomerKey])),
    FILTER (
        ALL (‘Date’),
        ‘Date'[DateKey] <= MAX (‘Date'[DateKey])
    ),
    FILTER (
        ‘Customer’,
        SUMX (
            ‘Filtered Products’,
            CALCULATE (
                COUNTROWS (‘Internet Sales’),
                ALL (‘Internet Sales’),
                ‘Internet Sales'[CustomerKey] = EARLIER (Customer[CustomerKey]),
                ‘Internet Sales'[ProductKey] = EARLIER (‘Filtered Products'[ProductKey]),
                FILTER (
                    ALL (‘Date’),
                    ‘Date'[DateKey] <= MAX (‘Date'[DateKey])
                )
            )
       ) > 0
    )
)

This returns all of the products that were bought for the analyzed product in question. A similar calculation that is going to return the count of products for everything that wasn’t bought looks like this:

Number Non Sales:=CALCULATE (
    COUNTROWS (FILTER(‘Customer’, CALCULATE(COUNTROWS(‘Internet Sales’))=0)),
    FILTER (
        ALL (‘Date’),
        ‘Date'[DateKey] <= MAX (‘Date'[DateKey])
    ),
    FILTER (
        ‘Customer’,
        SUMX (
            ‘Filtered Products’,
            CALCULATE (
                COUNTROWS (‘Internet Sales’),
                ALL (‘Internet Sales’),
                ‘Internet Sales'[CustomerKey] = EARLIER (Customer[CustomerKey]),
                ‘Internet Sales'[ProductKey] = EARLIER (‘Filtered Products'[ProductKey]),
                FILTER (
                    ALL (‘Date’),
                    ‘Date'[DateKey] <= MAX (‘Date'[DateKey])
                )
            )
       ) > 0
    )
)

NOTE: When I initially did this, my Number Non Sales never returned any counts back. It was always blank (0). In my pivot, I had customer gender, and then the number of sales and non sales. After looking at it for a few minutes I realized you wouldn’t be able to use customer attributes in the pivot like that, and you would always have to use the Product table/dimension to browse the measure. Even the filtered products table wouldn’t work, you can only ever use the product table to browse the attribute. Moving to Analyze in Excel, I created a slicer for the product you would want to analyze, and placed Product Category on rows, and dates on columns. The values are the two measures, Number Sales and Number Non Sales. A screen capture is below:

image

 

What’s really nice about this is that you can use it to analyze any product in the table that you would want to analyze. Another way of looking at it is “A brief introduction into data mining in tabular.” If I’m analyzing the Touring 1000- Blue bike, I can tell that 48 out of 61 people have purchased additional accessories. I can then double click to get the list of the 13 customers who haven’t bought any accessories, and then send them an incentive to purchase one and increase my sales, knowing that they are more like than any of my other customers to purchase one.

Dynamic Security in Tabular database

Tabular databases, like multidimensional, have the ability to create roles for additional security against our databases for individual business users. What’s more, it’s even easier to set up. Like other things in tabular, gone are the 9 or 10 different tabs, basic/advanced toggling etc. Roles in tabular and DAX use two functions to establish and manage security: the USERNAME DAX function and the LOOKUPVALUE DAX function. Lookupvalue is quickly becoming one of my favorite DAX functions, but that’s a different story. 🙂 Something important to consider when

First we’re going to go into our database and add a table that we can use to map to what we’re securing by. For this example, I’m going to map between the employee dimension and the sales territory dimension. To keep it simple, though, I’m going to list out the employee names, rather than having a EmployeeKey/Sales Territory key bridge table. Start by creating a table called Dynamic Security with the employeekey, names, and userlogon fields. My script is below:

CREATE TABLE dbo.UserSecurity
    ([EmployeeKey] [int] IDENTITY(1,1) NOT NULL,
    [SalesTerritoryKey] [int] NOT NULL,
    [FirstName] [varchar](50) NULL,
    [LastName] [varchar](50) NULL,
    [UserName] [varchar](50) NULL,
    )
GO

 

Then let’s input some values into it.

INSERT INTO [dbo].[UserSecurity]
VALUES    (1, ‘Administrator’,”,’DATA0\administrator’)
INSERT INTO [dbo].[UserSecurity]
VALUES    (2, ‘Administrator’,”,’DATA0\administrator’)
INSERT INTO [dbo].[UserSecurity]
VALUES    (3, ‘Bob’,’Williams’,’DATA0\bob’)
INSERT INTO [dbo].[UserSecurity]
VALUES    (4, ‘Steve’,’Smith’,’DATA0\steve’)

 

Now that we have our table created, we can do the actual security implementation. Open up the tabular database that you wish to apply the security too, and import your table. We’ll select and open our existing connection:

image

 

Then select the user security table that we created in Management studio and click Finish.

image

 

Once it’s imported, we then want to join it to the appropriate tables so we can relate to our security table. For this example we’re just joining to the Sales Territory. A snapshot of this piece of the model looks like:

image

 

Right click on the User Security table and click “Hide from Client Tools”. Now we can create our role. Scroll up to the top and click on Roles: (screenshot below if you need it)

image

 

Create a New Role, and give it a name. In this case, I’m calling it “Sales Territory Users”, giving read access under the permissions. Once you’ve done that, click on the Members tab, and add the users we have in our table. Below is a screenshot of what mine looks like with the 3 members I have in my table:

 

image

Go back to the Row Filters tab, and find the User Security table listed. Under the DAX Filter column, type in the following:

= FALSE()

Now find the Sales Territory table, and type in the following formula:

=’Sales Territory'[SalesTerritoryKey]=LOOKUPVALUE(UserSecurity[SalesTerritoryKey], ‘UserSecurity'[UserName], USERNAME(), UserSecurity[SalesTerritoryKey], ‘Sales Territory'[SalesTerritoryKey])

What this formula does is to say, “for each value in the UserSecurity[SalesTerritoryKey] column, find the current logged in Windows user name, and match it to the UserSecurity[SalesTerritoryKey] column. And only show rows where they match”

 

My Row FIlters section looks like the following:

 

image

 

Click Ok, and it will validate and finish your changes. Then go up to “Analyze in Excel”. Select “Other Windows user” and pick one of the users you have in your list. For this example, I picked Bob:

 

image

 

Click Ok, and then drag over your sales territory in the pivot table that appears. Drag over a measure so you get some data, and WHAMMY! some dynamic security awesome-ness:

 

image

Parent Child Hierarchies in DAX

Previously I’ve blogged about parent child dimensions in multidimensional, and how they work both physically (see here) and how to create and configure them (see here). DAX offers this same functionality using formulas that Microsoft has generously created and given us. Kasper has blogged about these (here) but for my own purposes in retaining this information I’m going to blog about it here. 🙂

Essentially, DAX does with a function the same thing we do in multidimensional in regards to naturalizing out the hierarchy for performance improvements. Remembering that DAX runs mostly on memory, it makes it much more efficient to normalize out that parent child dimension and let xVelocity store it as its distinct values that it needs. The most efficient function for doing that is the PATH function. The PATH function takes the parent child dimension, and identifies each level that exists down to what it needs. An example is:

=PATH([EmployeeKey],[ParentEmployeeKey])

Like the USERELATIONSHIP function, the PATH function only takes two arguments: the child key and the parent key. After it’s input into a parent child dimension, your result looks like:

image

You can see how the PATH functions returns a delimited list of each level of the hierarchy. The next step is to normalize it out, via the PATHITEM function. The PATHITEM function returns the value of that value at a given position. An example:

=PATHITEM([PCPath],1,1)

This returns a value column containing the number of the top level of the hierarchy.

image

This is great, but by itself the number 112 doesn’t really tell us anything. What we really want to know is: “Who is 112?” to accomplish this we can use the LOOKUPVALUE function. Our revised formula looks like:

 

=LOOKUPVALUE(DimEmployee[EmployeeFullName], DimEmployee[EmployeeKey],PATHITEM([PCPath],1,1))

The LookupValue function returns the string value we specify when we tell it what the key value is. Now we can just return the employee’s name instead of the key value. Continuing this, populating it out for the remaining 5 levels we have in this hierarchy yields a table that looks like:

 

 image

We can now build a hierarchy in our design view, and we have a normalized out parent child hierarchy!

DAX UseRelationship Function

In DAX, a major design approach change has to do with role-playing dimensions. They don’t need to be built into your warehouse differently, but the way they are handled in creating formulas and browsing is much different. Using the date dimension (as it’s the most common role playing dimension) as an example, in Multidimensional role-playing dimensions are materialized during cube processing and the dimension is propagated out for each date in your fact table. Therefore, using OrderDate, ShipDate, and DueDate, when a multidimensional cube is processed, each of these dates become their own dimension within the cube. In tabular, no such physically materialized dimensions exist, and only 1 date dimension is created and is used. Logically, this makes sense if we think about how efficient tabular is at storing only distinct values with pointers to retrieve the current record set, but it is worth exploring what the trade offs are.

 

When a business user has a question about one of these dates, they are asking the same question, just the way we answer it is different. For example, take the below approach in multidimensional to answer the question of how many orders did I receive by day/month/year. We would simply take our materialized Order Date dimension, drag over our hierarchy, and expand it appropriately. The result would look like the below.

 image

 

The real question we’re asking here is “How many total orders did I have?” Date is merely an attribute of that fact, just as it in a table. Do we really need OrderDate as it’s own explicit dimension? Even more relevant, we’re limiting ourselves in this view in that we can only look at Orders by date right now, if we add number of shipments, for example, we’d be looking at number of shipments by order date, which doesn’t tell us what we’re really looking for. The calculation where we specify we want Orders is in our calculation, so we don’t need an explicit order date dimension.

 

Creating the same thing in DAX is much simpler. Creating a PowerPivot workbook, and bringing in the FactInternetSales table along with the date dimension, we’ve created the relationship from the fact table for OrderDate, ShipDate, and DueDate to our DateKey in the date dimension. I’ve also specified that ShipDate is the active relationship. Our table relationship in the PowerPivot diagram view looks like this:

image

 

The questions we want to answer is “Number of Orders” and given the above limitation in multidimensional, “Number of Shipments.” In DAX, we can use the function CALCULATE combined with USERELATIONSHIP to explicitly specify what relationship we want DAX to use when performing that calculaton. Bringing up the FactInternetSales in data view, navigate to the calculations section of the table and under Orderdate you want to create a function using both CALCULATE and USERELATIONSHIP.

 

NumberOfOrders:=CALCULATE(COUNT(‘FactInternetSales'[OrderDate]),USERELATIONSHIP(‘FactInternetSales'[OrderDateKey],’DimDate'[DateKey]))

 

The Userelationship function is particularly handy, as we can tell DAX to not use the default relationship and to use the relationship we specify instead. It only accepts two arguments: the table we are specify the relationship from (‘FactInternetSales’[OrderDateKey]) and the table we are specifying the relationship to (‘DimDate’[DateKey]).

 

Now under the ShipDateKey column in the fact table we’re going to do the same thing, just specifying shipments instead of orders:

 

NumberOfShipments:=CALCULATE(COUNT(‘FactInternetSales'[ShipDateKey]),USERELATIONSHIP(‘FactInternetSales'[ShipDateKey],’DimDate'[DateKey]))

Now create a pivot table, drag over a date hierarchy (or just the year/month attributes if you haven’t built a calendar hierarchy), and then drag over your new measures. The result should look like:

 

image

 

Coincidentally, since we’re using AdventureWorks both NumberOfOrders and NumberOfShipments have the same final grand total, but you can see how the number of orders and number of shipments changed by date. And since we’re using just the straight dimension (not a materialized Order Date dimension) we can also rest easy knowing that the business is getting the correct values when they just drag over date. You also get the added benefit that business users only have 1 date that they can select, so you won’t get the “Why are there so many?” question.