There are people (at least I assume they exist) that plan out the words flying out of their mouths before they actually start speaking. Those people lack imagination and an appreciation for the true art of throwing caution to the…
Guest Post by Scott at Tiny Lizard
Hold onto your hats, my friends. We have some pretty advanced stuff for you today!
If you host your workbooks on SharePoint, you are about to read some powerful techniques, and hopefully give you some “brain-fodder” for related ideas. Even if you aren’t using SharePoint today… it’s worth reading to see the types of things possible with SharePoint, then you can refer back when SharePoint enters your life.
I am going to show two techniques to allow end-users to have some level of interaction outside the bounds of the workbook… say, to drive data into the underlying data sources. The first technique is not nearly as fancy as the second…
Guest Post by Scott at Tiny Lizard
Maybe it is a sign of where I am on the Geek Scale compared to Rob, but where he considers EARLIER() to be a pretty hard function to understand, it just doesn’t bother me. At least it seems to have just one purpose in life.
Now, the VALUES() function on the other hand… well, that’s just some messed up stuff right there! Not only does nothing about it feel natural and intuitive to me, but it also seems to behave in completely different ways depending on how and where it is used.
Basically, every time I use it, I feel like I either got lucky, pulled a fast one, and that I’m a dirty cheater. So, at least I got that going for me.
Let’s look at some of the various usages.
Even Very “Sophisticated” Reports/Dashboards are Often “Couch Potatoes” in Practice
(They Sit There and Expect You to Do All the Work)
I’ve been meaning to blog this for a long time – it is, after all, one of my absolute FAVORITE things to talk about. Now, Scott beats me to it. But he does such a good job below that I don’t have much to add, except a few graphics here and there, like the one above.
Besides, how can I not love a blog post that starts off talking about how right I am? 
By nature, I am a rather skeptical person. When I first hear an idea, I generally think it is probably wrong. This isn’t one of my finer traits and I am sure it drives Rob insane, since he has a rather impressive track record of being correct. Thankfully, this blog entry is not about one of the times I assumed Rob was wrong…
It is actually the complete opposite. It is one of those times when Rob told me something and it just instantly SUPER resonated with me and got stuck in me. I suspect it will do the same for most of you.
If you were fortunate enough to hear Rob speak recently at PASS Business Analytics Conference, you have already heard this idea.
It is super typical for us, as report authors, to generate The Report. The Noun. We hand it off with pride. And The Report has all the information somebody could possibly need to make a decision. All of it. Row after Row. Column after Column. Unfortunately, even with the fanciest conditional formatting in the world, it is not clear… how is somebody supposed to look at The Report and actually… do something?
Because, at the end of the day (and yes, with a handful of exceptions like keeping the SEC happy), it is our hope that somebody looks at our reports and uses it to make a decision. To do something. To Verb.
Guest Post by Scott at Tiny Lizard Power Pivot 2013 is a bit, shall we say, aggressive with its desire to keep your reports up-to-date. Renaming a measure that isn’t even used yet?Please wait, while I refresh your reports. Adding…
Guess Post by Scott Senkeresty at Tiny Lizard
Just a quick and practical tip today.
We have a really typical looking Date table. However, we are going to be drawing some pretty charts summarized by weeks, and our business defines “end of week” at Saturday. So, we need a new column in our Date table that stores this “Week Ending” date for each row.
The first thought to occur to me was “well, for each Year&WeekOfYear, I just want to grab the max date”. That sounded easy enough… EARLIER() no longer scares me…
Guess Post by Scott Senkeresty
Rob is taking a much-needed vacation this week, so you get to hang out with me again. Hurray for you!
When we last Became One With Calculate, I said in comments that I would “work on a visualization/graphic”. I admit to spending far too much time trying to dream up the perfect visual, and kind of failing. I am sure the elusive visual exists, but for now, I would like to reinforce our understanding of CALCULATE() with a few more examples.
We will again be partying with the Adventure Works, against this simple measure:
[Total Sales] := SUM(Sales[ExtendedAmount])
[TotalSalesEurope] := CALCULATE([Total Sales], Territories[Continent] = “Europe”)
This boolean parameter (aka: true/false parameter, column filter) says “Hey, Mr Dax Engine, I really don’t care what filter you had on Continent… cuz now it is Europe”. Of course, we did nothing that would impact a filter on Product[Category], so each of the categories still have their own total sales.
I must admit… when I last wrote about CALCULATE(), I was thinking there was something fundamentally different and special about these true/false filters, compared to the table-style filters such as we see with FILTER() or ALL().
And indeed, they are kinda sorta almost special… in that they have a cute syntax and they have the potential to be much more efficient (in terms of speed).
However, functionally, the above measure is identical to the following measure:
=CALCULATE([Total Sales],
FILTER(ALL(Territories[Continent]),Territories[Continent] = “Europe”))
Guest Post by Scott Senkeresty
Hey, it starts out simple and powerful: CALCULATE is the SUMIF you always wished you’d had. It works in pivots. It’s the “anything IF.” It’s amazing, really, how many doors it opens.
Of course, CALCULATE is designed to be powerful in ways we can’t even IMAGINE in our first day/week/month of using it. You can spend years discovering all the things it can do – and that’s a good thing! But sooner or later you’re going to hit something with CALCULATE that makes you scratch your head – why is it returning THOSE results?
I myself entered this twilight zone with the Precedence Project – a series of posts that I quickly abandoned. It turns out that, practically speaking, you don’t need to achieve deep theoretical understanding of this stuff in order to achieve great results.
Below, however, Scott does a great job of resolving those mysteries. And he does so by “channeling” two old friends who live at the base of the Alps. Take it away, Scott…
All right, so I’ve read Rob’s book a few times. (Heck, I am credited as tech editor on it.) I’ve devoured PowerPivotPro University. So now what, I ask Rob?
“Go forth and conquer – data is your ocean,” is his answer. He’s a practical sort of guy. Me, though? I’m never satisfied until I’ve completely torn the machine apart.
So, as I hinted in my last post, I went to graduate school and spent a few intense days engrossed in Marco and Alberto’s book.
Guest post by Scott Senkeresty After completing this post on table queries, I celebrated by curling up with Microsoft Excel 2013: Building Data Models with PowerPivot. It’s a great book by Marco Russo and Alberto Ferrari. (I’ve read Rob’s book…
Guest post by Scott Senkeresty
Get it? “Median?” SO Funny!
Rob is on-site with a client this week, so the reins, mic, baton or other appropriate metaphor gets handed to me today. We get to today’s topic by means of a discussion on calculating a median in DAX:
Scott: That sounds easy. Just use TopN to grab half the numbers in ascending order, then another TopN against descending values to grab the final value(s) (Glossing over odd vs even number of data rows)
Rob: What are you going to do about ties?
Scott: <Blank Stare>
It turns out that calculating a median in DAX is pretty tricky. Rob contends that sneaky street fighting tactics are required to deal with ties…where in my heart of hearts, I believe an elegant solution exists. Hopefully we can get to the bottom of that in a future blog post.