Update on Table Queries

March 17, 2014

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 now multiple times, and I was even the tech editor on it, so it was time to step up and read the other leading book).

When I got to chapter 14 in the Italians’ book, two things happened:

  1. I saw page after page that looked incredibly similar to my blog post… making me feel like a complete imposter.
  2. I saw “the next level”.

The Italians don’t just bring data into a table via DAX.  They further manipulate the data (with standard techniques excel pros know and love), and then, link this newly created table back to their power pivot model for further analysis with DAX!  And it all responds to Refresh.   Totally.  Awesome.


TopN as viewed by DAX Table Queries

March 13, 2014

 
Guest post by Scott Senkeresty

image

Get it?  “Median?”  SO Funny!

Intro

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.

Read the rest of this entry »


Reverse Polarity RELATED()

October 3, 2013

 

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.

Scenario Background

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:

9-17-2013 2-56-59 PM

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.

Read the rest of this entry »


Other (Better) Ways to Get All Measures As Text

October 25, 2012

 
image

The Second Time I’ve Used This “Egg on My Face” Picture

Feeling Silly

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 Smile

Read the rest of this entry »


Gantt Reworked with ADDCOLUMNS, FILTER, GENERATE and SUMMARIZE

September 5, 2012

Guest post by David Churchward [Twitter]

DAX Studio, ADDCOLUMNS, FILTER, GENERATE and SUMMARIZE

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.

Read the rest of this entry »


Implementing a Dynamic Top X via slicers in Excel 2013 using DAX queries and Excel Macros

July 26, 2012

image

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.

Read the rest of this entry »