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.
Guest post by Ken Puls
I was recently working on an interesting thing in PowerPivot, and thought I’d share it in case it may help someone… A quick summary of the issue is that I needed to examine multiple records in a child table, and return a single result to the parent table if a condition was present; something that goes against the normal relationship flow.
I have a file that tracks the purchase and sale of land, as well as the respective property taxes and assessments that we get on an annual basis. It is set up like this:
At first glance this might look complicated… Each Parcel is assigned a unique Parcel Identifier, known as a PID, a key identifier that will never change for a piece of property. This info is then used to identify that parcel by the tax authority, the assessment authority, and our appraisers and accounting systems. In the case above, we can see that the PID from the Parcel table links to the Transaction, Taxes and Assessments tables. From the other side we’ve got the Key_Date that links back to the three tables as well, although it links to TranDate, TaxYear and AssessYear respectively.
The Second Time I’ve Used This “Egg on My Face” Picture
OK, on Tuesday I published a technique that I’ve been using for years now – a way to get a list of all measure formulas as text. And it took about five minutes for people to respond and tell me about all the better ways to do it.
I’d like to say that none of those ways were better than mine. But ALL of them were better
Gantt Hours measure debugging with DAX Studio – isn’t it pretty!
At the end of my last post in the Gantt Chart series, GANTT CHART WITH RESOURCE LOADING, I said that I would return to explain how the Hours measure worked. I took a brief diversion, almost foray, into the world of CRM PIPELINE FUNNEL CHARTS but I’m now back to explain the workings of that Hours measure. You can revisit PART1 and PART2 of this series to recap.
In the interim, I’ve been working on some “fine tuning” of that Hours measure. I’ll come onto the final solution in due course, but I’ll work through the workings of the Hours measure as we left it initially to explain why it needed tuning up!
For those amongst you with an appreciation of SQL, you might think of this like creating a DAX Equivalent LEFT JOIN between tables with a BETWEEN thrown in for good measure.
Our First Post on Excel 2013 Beta!
Guest post by… Kasper de Jonge!
Notes from Rob: yes, THAT Kasper de Jonge. We haven’t seen him around here much, ever since he took over the Rob Collie Chair at Microsoft. (As it happens, “de Jonge” loosely translated from Dutch means “of missing in action from this blog.” Seriously. You can look it up.)
1) Excel 2013 public preview (aka beta) is out, which means that now we’re not only playing around with PowerPivot V2 and Power View V1, but now we have another new set of toys to take for a spin. I am literally running out of computers – I’m now running five in my office. Kasper is here to talk about Excel 2013.
2) I’ve been blessed with a number of great guest posts in a row, and there’s already one more queued up from Colin. This has given me time to seclude myself in the workshop and work up something truly frightening in nature that I will spring on you sometime next week. But in the meantime, I hand the microphone to an old friend.
Back to Kasper…
Inspired by all the great blog posts on doing a Dynamic Top X reports on PowerPivotPro I decided to try solving it using Excel 2013. As you might have heard Excel 2013 Preview has been released this week, check this blog post to read more about it.
The trick that I am going to use is based on my other blog post that I created earlier: Implementing histograms in Excel 2013 using DAX query tables and PowerPivot. The beginning is the same so I reuse parts of that blog post in this blog.