PowerPivotPro is Coming to Houston

April 17 - 19, 2018


**Use the discount code “3ORMORE” when signing up 3 or more people.

APRIL 17 - 18

Foundations: Power Pivot & Power BI

Instructor: Austin Senseman

Super charge your analytics and reporting skills with Microsoft’s dynamic duo. Designed to handle huge volumes of data, these tools will transform the way you work. Two Days in our class and you are EMPOWERED!


  • Not just the “hard” skills, but also the “soft” stuff (when and why to use it, how to get the best results for your organization, etc.)
  • Learn Microsoft’s secret weapon behind Power Pivot & Power BI: DAX
  • You don’t need to be an IT professional – most of our students come from an Excel background
PowerPivotPro Training Classes - Houston
PowerPivotPro Training Classes - Houston

APRIL 17 - 18

Level Up Series: Advanced DAX

Instructor: Ryan Sullivan

Foundations taught us how to remove repetitive, manual work and make impactful insights. Advanced DAX is about making it rain money by better informing decisions!


  • Taught completely in Power BI Desktop
  • If Foundations is a 101 course, hands-on work experience with DAX is 201, and Advanced DAX is 301.
  • This class will teach you how DAX really works, how to build complex reports that are still digestible, and how to use that information to drive your business.


Level Up Series: Power Query for Excel & Power BI

Instructor: Ryan Bergstrom

Copy-paste? Dragging formulas down? SAME THING EVERY WEEK?… No more. Teach your computer how to build your reports for you. Set and forget!


  • This class will teach you how to connect to all of your data (no matter where it lives), shape it so DAX can run automagically, and have your computer remember the steps so you never have to do it again.
  • You don’t need to be an IT professional – most of our students come from an Excel background
  • Taught simultaneously in Excel and Power BI
PowerPivotPro Training Classes - Houston
PowerPivotPro Logo


Your First Co-Development Project

P3 has tried many different flavors of our brand of “consulting” through the years, but co-development projects that combine real projects with coaching and knowledge transfer have turned out to have been the sweet spot in terms of success.

Sometimes we are hands-on in the build process, guided by your superior industry expertise. Then sometimes we remain entirely hands-off but guide you in the build. The important part is that wherever possible, we work together and that neither party progresses things so far forward that the other feels left behind.

By reading this far, you have implicitly agreed to join me on a trial co-development journey. Today, you are going to drive, and I’ll be your navigator – and that’s a pretty apt analogy because we’re going off-road.

Co-development projects require a commitment from both parties regarding both time & perseverance. Today you’ll only need about 5 minutes of time to completely build out Phase I of this project. Regarding perseverance, you might just need to turn a blind eye to some ugly code while still paying attention to the hidden underbelly of Power BI that I’m going to show you.

We are going to produce this interactive workbook of every DAX function you have available to you. Good news right? Super practical. For me at least I’ve found it easier to refer to than the MSDN pages.

My version looks like this:

Now does that sound like something you might be interested in?

But I’ll do even better than that. Stick with me, and in my next post, we will do the same for every Excel function and then again for every M function. By the end of our project, you will have a single interactive go-to reference for all three languages!

Would that be F’n great? So great that wouldn’t it be called the Great F’n Project? What’s that Editor Reid? No, it wouldn’t be? Not very searchable? Oh OK then….

Welcome to the Great Function Project!



Now I want you to hit save…. as hard as you can…

So now comes the time to commit yourself to this project. Open up a completely blank workbook save it as The Great Function Project and lets GOOOOOO!!!!!.


You know when you’re typing in your SUM function and the Intellisense prompts you with some guidance? It’s helpful right? But how does it know all that stuff? It has to be somewhere deep in the bowels of the file structure right? That’s the metadata, and there’s a ton of it behind the scenes before you’ve even created a table.

We’re going to take our Power BI file and ask it to look inside itself to extract its Metadata. So so meta…

Did you know? Power BI and PowerPivot are virtually identical to SQL Server Analysis Service Tabular under the hood. So it makes sense that we will be connecting to Analysis Services it to find this useful metadata. So go ahead, your turn.


You should end up here… but we don’t yet have everything we need to connect the file with… uh itself.


So now that I’ve gotten you this far, I have to ask you, do you have DAX Studio installed? No? OK, that’s fine, our project has hit a minor speed bump. This happens, but changing course quickly is our specialty. We’re going to need DAX Studio for all of about 30 seconds.

So at this point in our co-development project, it’s your turn to take things over. Before we can go any further, I’ll need you to download DAX Studio, install it and then open it.

For our purposes, DAX Studio is a tool that can show you all the underlying structures and metadata buried beneath the surface of your Power BI file – it does a lot more than just that though.

Connect DAX Studio to the workbook you were just tricked into saving.


In the big white space aka the Query Window enter this query:


Now go up to the top right and run that query… I know what you’re thinking… what are we doing here? I explained that we were going to get the metadata from our file, well this is the first step.

Technet says that the DBSCHEMA_CATALOGS “identify the physical attributes associated with catalogs accessible from the database management system (DBMS).”…. uh cool, I guess.

I don’t know what that means. But what I do know is that DBSCHEMA_CATALOGS is one of 100+ Dynamic Management Views (DMV’s), each of which stores some key information/metadata about the content of our not-as-empty-as-first-thought workbook. In this case, it’s telling me the database name of the SSAS cube hidden inside our Power BI model.

Another example of a useful DMV is $SYSTEM.MDSCHEMA_FUNCTIONS. This is where some of that Intellisense info lives. We’re going to query that DMV a little later on, but not from DAX Studio, so we’re pretty much done in DAX Studio….


That wasn’t so bad now, was it? Take note of the localhost information in the bottom right indicated with the #2 above. Flip back to the Power BI workbook and enter whatever you are showing here as the Server in your SSAS connection window that should still be open.

Then come back to DAX studio and right click on the cell in the Catalog_Name field market with a #3 above, and then copy the contents. Now returning to the Power BI workbook, paste that into the Database information.


Lastly, expand the MDX or DAX query panel and enter:


I would normally just do a select * to get everything here too, but for some reason that didn’t work for me, so I reverted to handpicking the columns based on this reference.

You should end up with a screen looking something like this.


Hit OK. Boom goes the dynamite! We have functions!!!!


But um…. I guess I knew less DAX than I realized – I don’t recognize any of those function names. Click Edit to go through to the Query Editor, and we’ll get to the bottom of this.


If you scroll down though, you will start to see some familiar DAX functions eventually. It turns out that 1 value in the Origin column is an MDX function and the rest look to be DAX, so filter out the 1’s.


Important last step 1: Right-click on the query an uncheck ‘Include in report refresh.’ The Analysis Services details will change later on and break the queries, and a static import will be fine.

Important last step 2: Rename the columns to the names you see below. We want to make sure no later transformations will be required later on… In hindsight, beginning with DAX was a bad idea. M & Excel won’t be so fragile.


And that’s it – for now. Rename the table to DAX and then Close & Apply. Now go to town laying out your interactive DAX reference report in whatever way works for you.

Come back next week and bring your completed file with you. Next week (using a different and simpler technique) I want to help you bring, inside the same file, all 471 Excel functions and their definitions. Why Excel? Well, it is the world’s most widely used coding language, and well, we need to talk about this.


  Subscribe to PowerPivotPro!


This Post Has 21 Comments

  1. Mat – Great post.

    I’ve replicated but calculate 262 DAX Functions vs. 260 that you show.
    Difference is UTCNOW and UTCTODAY in DATETIME Group.

    Using PBI Desktop (Feb. 2018 version)

    Looking forward to Part II.

    1. Yes, I created this pre-UTC functions. That’s part of the beauty of this though, you can refresh the report later and once you upgrade, the new functions should show up…in theory.

      Of course, it isn’t quite that simple.. you’d need to update the server details all over again and hope the schema of the PBIX itself hasn’t changed significantly – but theoretically…. it’s refreshable.

    1. @Kristi: Same here- I changed column names based on table visual:

      = Table.RenameColumns(#”Filtered Rows”,{{“INTERFACE_NAME”, “Group”}, {“FUNCTION_NAME”, “Function Name”}, {“DESCRIPTION”, “Description”}})

      1. Ham . . . I don’t see a table visual. The last visual displaying for me is the disable load visual then below that is a Venn Diagram. I have double checked every visual and none show me the column rename. If I could, I would post a snip here but it won’t let me.

  2. Trying to follow along but can’t get this to work – Unable to connect Details: “AnalysisServices: A connection cannot be made. Ensure that the server is running

    1. Sometimes DAX studio times out for me and you might need to reconnect by going to the top right and clicking connect…. Sometimes that fails for me too, then I close reopen DAX Studio and that usually works.

  3. Mat, I have a pbi file titled “The Great Function Report” and also it shows the same title in the PBI column but then then nothing happens. Can u help me on this.

    1. Sorry for the late response Nazim – was busy onsite with a client all last week. Glad you figured it out and your own.

  4. “Connect DAX Studio to the workbook you were just tricked into saving.”

    I must be too smart even though I feel dumb because I don’t know what workbook you’re referring too.

  5. What is the DAX coding for the box in the top right corner with the concatenated function name and description? Thanks.

  6. What DAX are you using for the Function/ Description box on the top right? Or is it a custom col with a concatenate function and a selectedvalue?

Leave a Comment or Question