TopN as viewed by DAX Table Queries

 
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.

For today, I want to use Excel 2013 to show an easy way to “see” how TopN handles ties.image

Let’s start with this clearly important real world data on the awesomeness of various colors, displayed to the right.

I’m doing some iterative development of my measure… and as an intermediate step I just want to make sure TopN is working like I expect (and that I didn’t forget the ALL() surround my table parameter).

Now, there is no direct way to display those rows in Excel.  Maybe as an intermediate debugging step we write the measure wrapped by COUNTROWS to make it legal and admire our answer of “3”.

Here is our measure (which, btw, ignores the case where the same Color shows up more than once I the table)

Top3:=
COUNTROWS(TOPN(3, ALL(ColorTable), ColorTable[Awesomeness]))
image

And low and behold, our minds are blown!  I mean, we even picked 3 because the first 3 values of Awesomeness were all 1, and ties weren’t even going to be an issue!   How can there be 5 rows returned!?

Now I am left head scratching, and really want to know what rows DAX thinks it is counting!    Good news!  This is easy!  Much easier than implementing a proper median measure.

Display Rows via Table Queries

Shout-out to Zack Barresse who brought this technique to our attention!

Step 0:  This only works in Excel 2013, not in Excel 2010.  Sorry folks.

Step 1:  On the Data ribbon, select “Existing Connections”.   On the dialog that displays select the Tables tab, and find the Connection to your table and double click that sucker.

image

Step 2: Leave “Table” selected, place the data where you want, and click OK.

image

Step 3: Briefly admire your basically un-interesting table that got inserted:

image

Step 4:  Right Click your new table, select Table > Edit DAX.  Flip the command type to DAX, and get to the good stuff!   You call EVALUATE and the first parameter is a table.  Lucky for us, TOPN returns a table!  So we paste our TOPN in there…  and click OK…

image

Step 5: Admire the updated table.  Shake head.

image

So, we quickly see two things here:

  1. TOPN defaulted to descending.   We didn’t get 1,1,1… we got 10,7,2.   The fourth (optional) parameter to TOPN controls ascending/descending.  Guess I should have read the docs.
  2. And the reason we saw “5” was that, indeed as Rob said, ties are interesting.  All ties get returned.  The docs might mention that too Winking smile

Wrap Up

So there you have it.  If you are using Excel 2013, you have an interesting debugging technique at your disposal.  Hope you find that useful.

And for those wondering about that Median problem…imagine you have { 1, 2, 4, 4 }.  If TOPN did not return duplicates, you would just take the TOP3 ascending (1,2,4), then the TOP2 descending (2,4), AVERAGE those together (3) and dance.

Instead, that first TOP3 returns… all 4.  Then dogs and cats are living together… and you are totally not dancing.  A discussion for another day.

4 Responses to TopN as viewed by DAX Table Queries

  1. Andrew Todd says:

    Hi Scott –

    Thank you for the informative post! I have developed what I believe to be an elegant solution to calculation median in PowerPivot and would love to share it with you. Is there a contact email that I could reach you at for further collaboration?

    Thank you,
    Andrew

  2. Bertrand says:

    Those with Excel 2010 can use DAX studio.
    http://daxstudio.codeplex.com/

  3. David Churchward says:

    Brilliant Scott and great to read. Looking forward to seeing more.

    Cheers
    David

  4. Jon Acampora says:

    Very cool! Could this same technique be done in 2010 using Power Query?

Leave a Comment or Question