Quick Tips & Tricks « PowerPivotPro

Lookups based on date ranges

February 7, 2012

Another one from the Mr. XL Forum!

…and the word “forum” gives me an excuse for a blast from the past…

“What you are about to witness is real. The participants are not actors. They have agreed to dismiss their tech support cases and have their questions settled here, in our forum:
THE POWERPIVOT COURT

(Click here for theme music!)

The much-neglected calculated column

OK, neglected by me, not by everyone else. At Pivotstream I have the benefit of a great SQL team. Generally speaking, when I need a calculated column, I ask them to provide it. The benefits of doing that are documented several places on this site, including here and continued here.

But hey, not everyone has a database rapid-response team at their disposal Sad smile

So questions like this one come up a lot on the forums, reminding me of my blind spot:

You have two tables of data. In one, you have a list of your clients and their effective discount rate during particular date ranges – marked by a start and an end date. If the End date is blank, that discount rate is assumed to still be active.

Rates Table: Discount Rate per Client,
Over Distinct Date Ranges (Start and End Effective Date)

Then there’s a second table, listing Clients and the days that you called each of them:

Calls Table: Multiple Clients, and Multiple Calls to Each Client

Desired Result

You want to add a calculated column to that Calls table which contains that client’s effective discount rate on the date of the call:

We Want to Add the Highlighted Calc Column – What’s the Formula?

So… what’s the formula?

=CALCULATE(AVERAGE(Rates[Rate]),
   FILTER(Rates,
          Rates[Start]<=Calls[ContactDate] &&
          Rates[EffectiveEnd] >= Calls[ContactDate] &&
          Rates[Client]=Calls[Client]
   )
)

Hey, That’s a Lot Like a Measure!

Yeah, it IS a lot like a measure. It uses CALCULATE, the wonder function.

But in this case, we’re just using CALCULATE to apply filters in our calculated column.

Works basically the same way, though. The three clauses in the FILTER() function are all AND’d together using the && operator, so that only rows from the Rates table meeting the following three criteria are “kept” and then “fed” to the AVERAGE function:

The Start date in the rates table must be prior to (or equal to) the Contact Date in the Calls table.
The End date in the rates table must be after (or equal to) the Contact Date in the Calls table.
The Client ID in the rates table must match the Client ID in the Calls table

Note for those who desire deeper understanding: The biggest difference between this calc column formula and a similar formula we’d have to write in a measure is that in a measure, we’d have to use the VALUES() function wrapped around the right hand side of the criteria – VALUES(Calls[ContactDate]) rather than just Calls[ContactDate]). And we’d have to “protect” those VALUES functions with an IF(COUNTROWS()) since you can’t use VALUES() as part of an “=” test, except when there is only one value returned, or maybe use MAX() instead of VALUES(). The point is that in a calc column, we DO have a current row in the table, so we don’t need the wrapper function like we do in a measure.

What’s that EffectiveEndDate column?

Yeah I sneaked that one in. It’s another calc column I added to the Rates table:

You can see its formula in the formula bar in the image above. I just wanted to put a non-blank value in whenever End was blank. You could hard-code a date in there if you wanted, or use TODAY() and a much bigger number than 365. I just figured that setting a date one year in the future from today is good. Keep in mind though that TODAY() will only be re-evaluated when you refresh your data or force a re-calc.

Is There a Relationship Between Those Two Tables?

No, there is not. Neat huh? And really, there couldn’t be anyway – each Client ID appears more than once in each table, and PowerPivot doesn’t handle relationships on “many to many” like that. The date columns are even less “relationship friendly” since they don’t match up at all, and are based on ranges in one table and single dates in the other.

Download the Workbook

I’m trying to do this more often, especially with the forum questions.

Download the workbook here

4 Comments | Accounting/Financial Techniques, DAX, Quick Tips & Tricks | Permalink
Posted by powerpivotpro

Mini Post #3: Measures Can Return Text!

January 26, 2012

Yes, That IS a Measure

Third and Final Mini-Post for Today

Got a question from a friend yesterday that made me realize I should share this: in PowerPivot, your formulas do NOT have to return numbers. As I’ve shown several times, they can return dates, but they can also return just plain and simple text.

For instance, check out the formula for that Trend measure above:

[Trend] = IF([$ - Sales] > [$ - Sales YAG], “Up”, “Down”)

[$ – Sales] and [$ – Sales YAG] are both measures (“current sales” and “sales year ago.”) So this IF() is 100% dynamic and responds to whatever fields and filtering/slicing you’ve done on your pivot! If sales have gone up since last year, it returns “Up,” otherwise it returns “Down.”

One of those cool little tricks that comes in handy from time to time Smile

2 Comments | DAX, Quick Tips & Tricks, Usability and Appearance | Permalink
Posted by powerpivotpro

Data of Different Grains: A Followup

January 19, 2012

Sales Measure Returns the Same Value for Everything: We Solved the Calendar/Periods
Problem But Now We Have a Products/Category Problem

Quick Recap: We Created a Separate “Periods” Table

In last week’s post, I explained how PowerPivot can very easily help you solve the “Budget vs. Actuals” problem or any other problem where you have data sets of different granularities that you want to compare in a single report.

The crux of the problem in my example was that my Budget table only budgeted down to the Month level whereas my Sales table went down to the day level.

To solve that, I created a separate table that only contained months (no sales or budget data, just months), and assigned a “PeriodID” to each month. (I could have just as easily called it MonthId).

The Newly-Created Periods Table

I then linked that PeriodID column to the PeriodID column that already existed in my Budget table:

And then created a new PeriodID column in my Sales table using a formula:

Resulting in a three-table setup that looks like this:

Which then allows me to use fields from that Periods table on slicers (or on rows or columns) while using measures from both Sales and Budget in the same pivot:

And that, my friends, is a very useful thing indeed.

Could I have just added the PeriodID column to Sales and Stopped?

This is an important question for us to stop and consider. Once I had a PeriodID column in both Sales and Budget, could I have skipped the creation of the Periods table and just moved on?

No. I could not. And there are two reasons.

Reason #1: I can’t create a relationship between Sales and Budget.

Each PeriodID Appears More than Once in Each Table, So You Can’t Relate
Sales Directly to Budget or Vice Versa

That’s just the way relationships work. The matching column needs to be unique (no value appears more than once) in at least one of the two tables. No need to really worry about why, but if you think about how VLOOKUP works when you set the last argument to FALSE, it’s pretty similar.

Reason #2: Without a relationship, PeriodID from one table doesn’t work with measures from the other.

Look what happens to Budget measures if I use PeriodID from the Sales table:

PeriodID from Sales Table Results in Broken Budget Measures
(and a relationship warning)

Yep, all busted. And if I use PeriodID from the Budget table, I’ll get good Budget numbers but the Sales numbers will be hosed instead.

THAT is why the third, new, separate Periods table is required. When I use fields from THAT table on my pivot, I can use measures from both Sales and Budget at the same time and nothing is broken.

So we use the Periods table as a master filter of sorts, one that can drive filters down into both Sales and Budget.

“Master Filter” Table is a MUCH better description than “Bridge”

I debated using a “bridge” metaphor to describe the role of the Periods table but I think that’s misleading – a bridge helps you travel from point A to point B: A –> Bridge –> B. That would make you think that we’re “starting” on the Sales table and “traversing” the Periods table to get to the Budget table.

But that’s not how it works. Neither Sales nor Budget is “in charge” here – neither is the starting point. There are two separate paths here: Periods –> Sales, and Periods –> Budget. The diagram drives that home:

So you should think of the Periods table as being “in charge” here. Which makes sense, since you have to use it on the pivot, and can’t use PeriodID from either Sales or Budget.

Extending that Rule to Fields Other than PeriodID

This is the crux of what I left out of my previous post, and it’s an important rule to drive home:

When you have separate data tables that cannot be combined into a single table (as is the case with Sales and Budget), you CANNOT use row/column/slicer fields from one table in a pivot with measures from the other table. You MUST have a separate “master filter” table, and use fields from THAT table in your pivot.

So does that apply to fields other than PeriodID? You betcha. Guess what happens if you use a Product Subcategory or similar field from the Budget table in a pivot that contains a Sales measure (or vice versa)? Yeah, same problem as trying to use PeriodID from Sales as we did above:

What’s the solution? A separate master filter table for Products!

And that may be as simple as a single column:

Wash, Rinse, Repeat

Once you have measures from two different data tables, every row/column/slicer field you use requires a separate master filter table.

It may sound tedious but it’s actually quite quick. To easily create such a table you can even just use a pivot:

Put the ProductSubCat field from either Sales or Budget on rows.
Copy/paste that column of unique values into PowerPivot as a new table.
Create the relationships to Sales and Budget.
Use fields from the newly created master filter table in your pivot. Done.

(Better to get someone to add a new view or table to a database for you if that’s available, but copy/paste works well otherwise).

Also Useful for Single Table Situations!

One last point: even when you only have a single table of data, like Sales, it’s often still quite useful to create separate master filter tables.

Why is that?

Because when you create master filter tables, it gives you the opportunity to remove lots of columns from your “big” table.

Imagine a sales table that, in addition to columns like Quantity and Amount, also contains columns like CustomerID, CustomerGender, CustomerAge, CustomerAddress1, CustomerAddress2, etc.

If you leave CustomerID in the sales table but “move” all of the other customer columns into a separate Customer table, and then link the two tables by Customer ID, you can reduce the number of columns in your Sales table by a significant number.

And that can sometimes shrink your files dramatically, AND speed them up. See this post for an example.

Leave a Comment » | Custom Calendars, DAX, Quick Tips & Tricks | Tagged: PowerPivot Power Pivot Microsoft SharePoint BI Excel Business Intelligence | Permalink
Posted by powerpivotpro

Sales/Budget: Integrating Data of Different “Grains”

January 12, 2012

Latest Article for CIMA Insight

Some of you know that I’ve been writing a series of articles for the Chartered Institute of Management Accounts, aka CIMA. Up until this point those articles have either focused on the organizational impact, the “why” of PowerPivot. Things like how it contrasts with traditional BI and traditional spreadsheets. How it saves costs and delivers more.

And then I started to introduce basic topics that have already been covered at length here on this blog, just simple how-to-get-started stuff.

But this month is the first time I wrote something for CIMA that I have not really covered here.

The Problem: 60k Sales Rows vs. 2k Budget Rows

Sales Table with 60 Thousand Rows

Budget Table with Two Thousand Rows

The Desired Result: A Single Unified Report

The Solution

Here’s where I have simplified it a bit for the introductory audience. I likely have a Calendar table in a well-designed model, and that doesn’t appear in the diagram. I also likely have to solve for more than just Time granularity – the Budget is also likely less granular at the Product level as well.

But to introduce this powerful concept, I think a simple example is best so I kept it clean.

Click here to read the full (short) article and see the details of how I create the linkage between the two tables.

Switching Subtotals On & Off Dynamically Using DAX in PowerPivot

January 5, 2012

Guest Post by David Churchward

Subtotals on or off for a field - They’re the options that you get. That’s probably great for a lot of situations, but I would put money on the majority of readers of this post having had a situation where they’ve asked “where’s the option to not display a subtotal when there’s only one record being shown?”. Frustrating isn’t it? You end up with a load of subtotals simply repeating the same value as it’s own one subset member because one of your other report elements has a subset of data that does need subtotalling. You can, of course, toggle the show and hide for each member, but wouldn’t it be better if it was dynamic?

Fortunately, DAX has the answer. It’s not the answer that I would have hoped for because this one feels like it should be an option setting somewhere. However, we can deliver the same functionality by writing it into our measures.

In this post, I’ll explain how to create a dynamic measure that works out whether a subtotal is needed and displays that subtotal accordingly. I’ll be using the report that was created as part of the Cash Flow Statement post.

The Subtotal Issue

Let’s refer back to the final report that I created for the Cash Flow Statement.

In the column CF3_Name, the only subtotal that I want to see is the one on Working Capital as this is the only one that has any lower level detail. You’ll notice that all other areas are carrying subtotals which only serves to repeat the number above it.

COUNTROWS and COUNTX

In order to solve this problem, you obviously need to know how many subset elements there are that would make up your subtotal. It would be reasonable to jump straight for COUNTROWS. Let’s examine that quickly with a measure that we’ll call CF4_Headings_No in the hope that it will tell us how many headings are in our subset.

CF4_Headings_No

= COUNTROWS(VALUES(CF4[CF4]))

On the face of it, that doesn’t appear to give us what we want. We have the same results for all headings and CF4_Name repeats all of the same headings. The reason that this happens is because CF3_Name and CF4_Name are held on separate tables. Although they hold relationships to the same table, PowerPivot doesn’t pre-determine which combinations of these fields exist, even at run-time.

So let’s look at what happens when these headings are held on the same table with the same COUNTROWS formula (but obviously changing table name etc!).

That looks better right? We can see all of the correct combinations of CF3_Name and CF4_Name. On the one hand, this is good because we can distinguish the fact that Working Capital is the only element that has more than 1 subset item and therefore the only element that requires a subtotal.

However, there’s one big downside here. For all other sections, we can’t distinguish between a subtotal line and a detail line. There’s a couple more downsides here too:

I have to create a table that holds all of the combinations of these headings. This isn’t the biggest issue as I have a table called GL_Headings that holds the codes associated to these headings. I can create a RELATED()measure in my PowerPivot dataset to return the heading name. However, why bother if we don’t have to?
Don’t underestimate the power of the function COUNTROWS(VALUES(TABLE[FIELD])) returning a value of 1 which happens when headings are held as a separate table. This opens up a whole host of functionality and it’s probably one of the most important aspects of PowerPivot in my opinion.

So, what’s the final analysis of COUNTROWS

Using COUNTROWS against a separate table with unique values for each heading gives us the means to distinguish between subtotal sections and detail sections of our report.
Using COUNTROWS against a combined table with heading combinations tells us the CF3 headings that carry detail where we do want to see a subtotal.

I’m sure you’ve probably deduced where I’m going with this as the combination of these two elements probably tells us what we need to know to figure out whether a subtotal is required. Unfortunately, this is not entirely true. The problem is that the combination of these elements won’t operate in context unless the context is directly applied against the heading table (ie you put a slicer on the report for the cash flow heading elements). What about a date slicer? If you apply a date slicer which is a separate table, these measures won’t react.

Bring on COUNTX

Let’s jump straight into the DAX on this one and see what it’s doing by creating a measure called CF3_Total_Reqd.

CF3_Total_Reqd

=COUNTX(CF4,[Cash_Flow_Statement])

COUNTX is analysing our CF4 table in the context of our measure [Cash_Flow_Statement] (as calculated in the Cash Flow Statement post). The context applied to [Cash_Flow_Statement] is carried across to our COUNTX formula. We run this against our CF4 table because we want to get the number of detail heading names that carry a value in our [Cash_Flow_Statement] measure.

So what do we get:

Our CF3_Total_Reqd measure using COUNTX is telling us how many headings are used in our dataset for the MonthEndDate shown on our report. This is exactly what we want. What’s even more special is that if, in a particular month, our [Cash_Flow_Statement] measure only has one heading in the Working Capital section, the Working Capital Total would return a value of 1, which is a scenario when we want to switch our subtotal off.

But there’s a problem

Our COUNTX measure, as it stands, is returning a count where we have a heading, but it isn’t telling us when we have a valid blank entry at the CF4_Name level. As a result, any CF3 category that doesn’t carry CF4 heading detail, isn’t telling us anything. I know we have Operating Profit elements, but we’re not getting any counts returned.

But what if we specify the column that we’re analysing in our CF4 table? We can do this by adding in VALUES and specifying the column in our CF4 table.

CF3_Total_Reqd

=COUNTX(VALUES(CF4[CF4_Name]),[Cash_Flow_Statement])

We have a result! And our Working Capital section is behaving itself too!

And now for the Subtotal Switch On / Off Measure

Consider our two measures:

CF3_Total_Reqd this tells us when a total is required ie CF3_Total_Reqd > 1
CF3_Total_Reqd also tells us where detailed elements exist, ie returns a value of 1, but this measure does the same for those associated subtotals. CF4_Headings_No, at the same time, helps us distinguish, generally and irrespective of context, between detail and subtotal levels on our report ie CF3_Total_Reqd = 1 AND CF4_Headings_No = 1

If we combine these elements, we get a measure that we’ll call Cash_Flow_Heading_Tidy

Cash_Flow_Heading_Tidy

=IF(

[CF3_Total_Reqd]>1||

([CF3_Total_Reqd]=1&&[CF4_Headings_No]=1),

[Cash_Flow_Statement],

BLANK()

)

That gives us what we want, right? Well not quite. The Cash Flow From Operations section is now right. However, the other sections in my CF2_Name column have reached the criteria for their totals to be eliminated. If I don’t want this effect (and I don’t because this is a major total), I have to specify for my Cash_Flow_Heading_Tidy to directly include those totals.

Cash_Flow_Heading_Tidy

=IF([CF3_Total_Reqd]>1||

([CF3_Total_Reqd]=1&&[CF4_Headings_No]=1)||

COUNTROWS(VALUES(CF3[CF3_Name]))>1,

[Cash_Flow_Statement],

BLANK()

)

There you have it. All subtotals behaving properly.

Leave a Comment » | Accounting/Financial Techniques, DAX, Quick Tips & Tricks | Tagged: BI, Cash Flow Statement, COUNTROWS, COUNTX, DAX, Excel, PowerPivot, Subtotals | Permalink
Posted by David Churchward

Adding State Alpha Sort to the Sort By Slicer Trick

October 18, 2011

How can we call something SuperFriendly
if it’s not sortable by State?

Sorting by State! How did I miss that?

In a comment on Thursday’s post, Janet asked an excellent question: what about sorting by state name?

Awesome awesome awesome. Thanks Janet – of course the people who use this report are going to want that.

Attempt #1 – Text Measures

Given that my trick relies on sorting by HiddenSortMeasure, I somehow have to get the state name reflected in that measure. So I need a measure that represents StateName.

I don’t think this is widely known, but measures CAN return text. Let’s write a measure that simply returns the name of the state:

[StateNameMeasure]=

IF(COUNTROWS(VALUES(States[FullStateName]))=1,
VALUES(States[FullStateName]),
BLANK()
)

If you are wondering how that formula works, by the way, I highly recommend checking out the post where I explained IF(VALUES).

If I add that measure to the pivot and sort by it, I get:

Well, the measure worked. But the sort order is awful. Why is Missouri ahead of Alabama? A programmer will surely scold me for asking that, but seriously, I have no clue.

OK, let’s try another technique.

Attempt #2 – Add a column to the States Table

Getting that column into PowerPivot was actually a bit trickier than I’d like it to have been, since this was a copy/pasted table. If this were a serious production application, I would have been using SQL as the source for this table, and I would have asked my database colleagues to add it for me.

But since this is, ahem, UFO data, I pasted a second, two-column States table into PowerPivot – StateName and AlphaOrder columns and related it to my original States table (and be sure to treat the new table as the Lookup table!) Then I used =RELATED() to add it to my original States table.

OK, now time for an AlphaSort measure:

[AlphaSort]=

MAX(States[AlphaOrder])

And that DOES sort properly:

BTW, I could have used MIN(), or SUM(), or even AVERAGE() instead of MAX(). I just needed something that returns the number.

Now I just need to add it to my slicer table, and add another clause to the IF() in my original [HiddenSortMeasure], and…

It’s Alive!!!!

A bit trickier than sorting by the other columns, yes, but doable.

2 Comments | DAX, Quick Tips & Tricks, Slicers | Tagged: PowerPivot Power Pivot Microsoft SharePoint BI Excel Business Intelligence | Permalink
Posted by powerpivotpro

User-Friendly Report Sorting With Slicers!

October 13, 2011

Yeah, It Really Works!

With great Excel skill comes… insensitivity to others’ lack of it

Let’s say you are a monster Excel pro. You’re a pivot master. Nothing is beyond you – even the more complex features of Excel seem easy. That’s obviously a huge strength, an asset. And PowerPivot magnifies those powers – it gives you a much bigger stage, makes you more important, and extends the reach of your work to a lot more people than before. Great stuff.

But your skill level can also blind you. The people who consume your PowerPivot applications and reports are not NEARLY as Excel-savvy as you. The things you take for granted are often hard for them, sometimes even scary to them.

That can be frustrating of course, but remember: if they understood Excel as well as you’d like them to, there wouldn’t be so much need for your skills.

Failure to understand a report is not THEIR fault. It’s YOURS.

When you share a report with someone and they can’t figure it out, your first response may very well be to groan or sigh (inwardly), and mutter to yourself about how some people can’t seem to tie their own shoes. Then you put on a helpful face and go explain to them how to use the report. You may even say something like “hey, it’s actually pretty easy once you understand.”

That’s a tempting trap. I’m not above it, trust me. But I know that’s the wrong first instinct, to explain to them the mechanics of how to do it, or to tell them it’s actually pretty easy. The right first instinct, the one I am constantly reinforcing with myself, is to think “how can I make the report easier to understand?”

And as your work becomes more important, and makes its way further up the leadership hierarchy of your organization, it becomes even more critical to have the right first instinct.

Example: “I Can’t Sort the Report!”

Let’s say you have published the following mission-critical pivot report on UFO Sightings in the United States:

And one of the report consumers says to you “great, but how do I sort by Average Sighting Length instead?”

Well, you and I (the Excel pros) both know about that little dropdown don’t we?

This Dropdown Scares Most People. Seriously, it Does.

But that dropdown is scary. Seriously. The only people who don’t find it scary are Excel nerds like us.

And we, the Excel nerds, also know that we can right click in the Avg Sighting Len column and choose a sort option. Normal people don’t know that. Furthermore, that doesn’t work on SharePoint. And really, the report consumer is used to simply clicking on column headers to sort – in just about every single application they have ever used… except for Excel.

So in cases where sorting is important, can we give them something a little friendlier? Yes we can.

Step 1: Create Two Dummy Tables for Slicers

First table just lists all the measures you’d like the user to be able to sort by. Second table is just Ascending/Descending (although as an added boost to friendliness, I came back and changed those to Largest to Smallest/Smallest to Largest because Ascending/Descending often confuses even me!)

Now you can add them as slicers on the report, even though they don’t do anything yet:

Step 2: Create Measures that Detect User Selections on Those Slicers

[SelectedSortMeasure]=

IF(COUNTROWS(VALUES(SortBy[Sort Table By]))=1,
VALUES(SortBy[Sort Table By]),
“Total Sightings per Year”
)

[Selected Sort Order]=

IF(COUNTROWS(VALUES(SortOrder[Sort Order]))=1,
VALUES(SortOrder[Sort Order]),
“Largest to Smallest”
)

Both of those merely return the caption of whatever is selected. And if more than one thing is selected on a slicer, it returns a default value – Total Sightings per Year in the first measure.

Step 3: Create a 1,-1 Measure Based on Sort Order

[SortOrderMultiplier]=

IF([SelectedSortOrder]=”Smallest to Largest”,-1,1)

If the SortOrder measure defined above returns “Smallest to Largest” then this measure returns –1. Otherwise it returns 1.

Step 4: Create a Branching Measure Based of the “Sort Table By” Slicer

[HiddenSortMeasure] =

IF([SelectedSortMeasure]=”Avg Sighting Len (Mins)”,
   [Avg Sighting Length in Mins],
   IF([SelectedSortMeasure]=”Sightings per 100K Residents”,
      [Sightings per 100K],
      [Sightings per Year]
   )
)* [SortOrderMultiplier]

This measure returns an entirely different value based on whatever the user selects on Sort Table By. Sometimes it “mimics” one measure, other times another.

And note that last line – it multiplies [SortOrderMultiplier], which is 1 or –1, by the whole thing.

Step 5 – Add The HiddenSortMeasure to the Pivot, Sort By It

Sort the pivot by that measure. Notice how it is the negative version of the Total Sightings per Year measure? That’s expected based on the slicer selections.

Step 6 – Hide that Column of the Spreadsheet

And the result:

This is actually really easy. Took a lot longer to write this post than it did to add to the report.

THIS TOPIC CONTINUED:

Adding “sort by state name” to this report

Try this report out live in your browser!

Next step, of course, is to make this thing look better, but that’s another post.

17 Comments | DAX, Quick Tips & Tricks, Usability and Appearance | Tagged: PowerPivot Power Pivot Microsoft SharePoint BI Excel Business Intelligence | Permalink
Posted by powerpivotpro

DAX Autocomplete Stops Working–A Workable “Fix”

September 27, 2011

“There, good as new.”

By Now, You’ve Probably Seen This

So you’re typing along, writing a DAX measure. And suddenly, poof! Autocomplete stops working.

This happens to me all day, every day. Especially when I use the [Measure]( filter expression ) syntax. Here’s a quick “before and after” example:

Autocomplete is Working for Table/Column Names

Same Table Name, But Autocomplete is NOT Working

Grab Your Duct Tape…

A guy named Scott Kaylie showed me this about six months ago and I am just now getting around to posting it. Yes, my backlog of posts has in fact grown to be at least six months long Sad smile

Anyway, here’s the trick. Just put a * in between the measure and the open parenthesis, as if you were going to multiply the measure by another expression:

OK, so then just go ahead and finish writing the measure, leaving the * in there:

And then, at the very end, go back and remove the *…

Voila. Ugly yes, but much better than having no workaround. Thanks Scott.

Any other questions please consult the following home repair manual:

1 Comment | DAX, Quick Tips & Tricks | Tagged: PowerPivot Power Pivot Microsoft SharePoint BI Excel Business Intelligence DAX | Permalink
Posted by powerpivotpro

Detecting ALL() – Detecting When Not Filtered

September 20, 2011

“I like to carry it, you never know when you’re gonna need it.”

-The much-missed John Candy as Uncle Buck

A technique that you may need someday

File this under “you may never need this, but when you do, you’ll know it immediately.” I’d call this one a solid 4 on the DAX Spicy Scale.

Why I needed this: I had two different calendar tables, one at the Date level and one that was a custom calendar, Periods table. Most of the measures in this model are written to be used with one table or the other, and I never have to “cross the streams.”

But then I ran into a case where a measure I had written to be used with the Periods table, suddenly needed to also be used on a pivot that was only filtered by Dates. And I didn’t want to write a new version of this measure (for reasons that are mostly irrelevant here).

Of course, when I put the Period-focused measure on a Date-focused pivot, and there were no fields from Periods on the pivot, well… the Period-focused measure returned junk.

What I decided to do, then, was detect if Periods was the aptly-named “Sir Not Appearing in This Film” and then assign a Period in that case. In other words, detect if the Periods table was not on the pivot, and if not, FORCE a Period value into the evaluation of my measure.

Detecting ALL(), or the Absence of Filter on a Field or Table

Here is the final measure formula I used, color-coded for identify its parts:

IF(COUNTROWS(Periods) = COUNTROWS(ALL(Periods))-1,
   CALCULATE(My Original Measure,
      FILTER(Periods, Periods[Period] = [LatestPeriod])
   ),
   My Original Measure
)

Let’s go part-by-part:

The Detector

IF(COUNTROWS(Periods) = COUNTROWS(ALL(Periods))-1

The “detector” counts the rows of Periods in the current pivot context, and compares that to the number of rows in the Periods table with all filters removed by ALL(). Pretty straightforward right?

So… why the –1 at the end? The reasons for that are slightly academic… academic enough that I don’t really want to understand in great depth. Let’s go with the short version: in this case, when I counted the rows of ALL(Periods), ALL() was kind enough to include the “blank” row of the Periods table.

What’s that? You say you don’t HAVE a blank row in your Periods table??? Well, neither do I. But you MIGHT have some rows in your Sales table that have a blank value for the [PeriodID] column. Or maybe you have rows in the Sales table that DO have a [PeriodID] value, but that value does not appear in your Periods table. Either way, you implicitly DO have blanks in your Periods table, and COUNTROWS(ALL()) decides to tell you about it. So you’ll subtract one and like it, soldier!

(I have not tested, honestly, whether you always need – 1 in this detector. If your Sales table is perfectly clean, maybe the – 1 is not needed, and maybe it is. Someone let me know OK? And I bet three-to-one that the answer comes from Italy.)

The Original Measure

My Original Measure

OK, this part IS straightforward. Whatever my original measure was named, or perhaps its full original formula, appears here. Moving on…

The Filter for the “No Periods Selected” Case

FILTER(Periods, Periods[Period] = [LatestPeriod])

OK, in the case that Periods is absent from the pivot, I take the original measure and then use the FILTER function to pretend that the Periods[Period] column IS on the pivot, and filtered to a value matching the most recent period, as calculated by my [LatestPeriod] measure.

But really, this part is going to be VERY different based on the circumstance. Maybe you want to use a completely different measure, for instance. Or set Periods to the first period this month. Or the period corresponding to the current filter context from the Date table.

I merely included the “meat” of what I did here to drive home the intent.

Two Notes

One – note that there is NO difference between “the Periods table is not used on the pivot at all” and “the Periods table IS on the pivot but unfiltered in the current context.” So if you have Periods on a slicer but nothing is selected, the detector will “go off.” And if you have Periods on rows, the detector will still “go off” in the grand total cell of the pivot (and maybe in certain subtotal cells as well).

Two – I was messing around in this area when I discovered the need for the long-simmering Precedence Project. In other words, when you start messing around with overriding filter context like I did with the FILTER() function above, and you’ve got a number of tables and relationships in play, every now and then you see something you don’t expect. For that reason, I plan to return to the Precedence Project shortly.

4 Comments | DAX, Quick Tips & Tricks | Tagged: PowerPivot Power Pivot Microsoft SharePoint BI Excel Business Intelligence DAX | Permalink
Posted by powerpivotpro

Catching Multiple Slicer Selections in a Formula

September 8, 2011

A long time ago I did a post on using slicer selections in Excel formulas. That technique only worked when you select single values on slicers, though – any more than one and the dreaded “(Multiple items)” foils your well-laid plans.

How to handle that multi-select case became a very common question – in email and posted as comments.

Awhile back I responded privately to one of those requests but haven’t had time to post the solution. So here goes.

The Solution, Summarized

First, here’s a picture of the solution I came up with. For fun, see if you can figure out what I’m doing just by looking at it:

Everything in green is visible to the final report consumer (or at least, you can choose to make it so). Everything in grey is stuff you likely hide – either by hiding columns or by placing on a hidden sheet. The SKUID field is on the slicer (that is hooked to both pivots) and is also on the row axis of the hidden pivot, but is not included on the visible pivot.

Here’s the same spreadsheet, but zoomed in and with formulas visible:

I went ahead and uploaded this workbook so you can take a look in a hands-on manner.

CLICK HERE TO DOWNLOAD THE WORKBOOK

Note that this was a PowerPivot workbook originally but I think I nuked all the data out of it, so you won’t be able to manipulate the pivots. No worries though – the formulas here are 100% of the technique and should work with any pivot.

I’m pretty sure I could simplify this a little bit if I tried, but probably not by much. I eagerly await everyone’s constructive input Smile

3 Comments | Quick Tips & Tricks, Slicers | Tagged: PowerPivot Power Pivot Microsoft SharePoint BI Excel Business Intelligence | Permalink
Posted by powerpivotpro

M	T	W	T	F	S	S
« Jan
		1	2	3	4	5
6	7	8	9	10	11	12
13	14	15	16	17	18	19
20	21	22	23	24	25	26
27	28	29