clip_image002

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!

clip_image003

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!!!!!.

clip_image004

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.

2018-02-27_1255

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

clip_image006

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.

clip_image007

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

Select * from $SYSTEM.DBSCHEMA_CATALOGS

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….

clip_image008

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.

clip_image009clip_image010

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

select [FUNCTION_NAME], [DESCRIPTION], [ORIGIN], [LIBRARY_NAME], [INTERFACE_NAME] from $SYSTEM.MDSCHEMA_FUNCTIONS

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.

clip_image011

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

clip_image012

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.

clip_image013

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.

clip_image014

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

2018-02-27_1517

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 programming language, and well, we need to talk about this.

clip_image015

Microsoft’s platform is the world’s most fluid & powerful data toolset.  Get the most out of it.

No one knows the Power BI ecosystem better than the folks who started the whole thing – us.

Let us guide your organization through the change required to become a data-oriented culture while squeezing every last drop* of value out of your Microsoft platform investment.

* – we reserve the right to substitute Olympic-sized swimming pools of value in place of “drops” at our discretion.

  Subscribe to PowerPivotPro!
X

Subscribe

Mat Herring

Mat is a US CPA and Australian Chartered Accountant with 15 years’ experience across global financial services giants and bootstrapping startups alike. During that period, the Microsoft Power Suite became the secret weapon that allowed Mat to transition from competent finance manager to cross-functional CFO. A real life example of the career empowerment that can take place when a bean counter gets hands on with the analytics. 

This Post Has 30 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?

    1. No calculated column, though that’d work fine, but yes a basic concatenate measure all the same. First Non Blank Function Name & then CALCULATE First Non Blank Function Description but filtered to the Function name.

      VAR FunctionName = FIRSTNONBLANK(‘DAX'[Function name], 1)
      RETURN
      Blackboard =
      FunctionName &
      ” – ” &
      CALCULATE(
      FIRSTNONBLANK(‘DAX'[DAX Function Description], 1),
      FunctionName
      )

  7. Hola Mat muchas gracias por la excelente informacion, te comento que he realizado como comentas pero he descargado la ultima version de Dax estudio 2.7.4 y en el paso tres aun me sale que esta “Establishing Connection” que puedo hacer para poder continuar con el post.
    gracias por tu ayuda. saludos.

  8. Thanks for great post Matt!

    Been unhappy about the need to fire up DAX Studio and manually copy/paste connection parameters (and then re-do it if you need to update the model as on each restart port/db name change) – fortunately, this can be (almost fully) automated. I’ve used hint from this post http://biinsight.com/connect-to-power-bi-desktop-model-from-excel-and-ssms/ to find port number from msmdsrv.port.txt file in PowerBI temp directory. _Almost_ fully as temp path is hard-coded – haven’t found the way to read environment variable from inside PowerBI… As long as .pbix file is opened under same user account it will be OK.

    First data source returns the port number. We get the contents of AnalysisServicesWorkspace directory inside PowerBI temp directory, take most recent subfolder (in case PBI crashed earlier… you might have multiple subdirectories there), get msmdsrv.port.txt file from Data subdirectory there and read port number from it. Script below: Get Data – Blank Query, copy/paste in Advanced Editor, name it as AnalysisServicesPort and don’t forget to update temp directory argument in Folder.Contents call!

    let
    Source = Folder.Contents(“C:\Users\%%%%%%%\AppData\Local\Microsoft\Power BI Desktop\AnalysisServicesWorkspaces”),
    #”Sorted Rows” = Table.Sort(Source,{{“Date created”, Order.Descending}}),
    #”Kept First Rows” = Table.FirstN(#”Sorted Rows”,1),
    WorkspaceDir = Table.SingleRow(#”Kept First Rows”)[Content],
    Data = WorkspaceDir{[Name=”Data”]}[Content],
    #”msmdsrv port txt” = Data{[Name=”msmdsrv.port.txt”]}[Content],
    PortNumber = Number.FromText(Text.FromBinary(#”msmdsrv port txt”,TextEncoding.Utf16))
    in
    PortNumber

    Second a function returning results of DMV query from local PBI SSAS instance – it reads list of available databases on local SSAS, takes the first one and runs DMV query against it. Needed to make it a function to avoid annoying “Query references other queries or steps and so may not directly access a data source” error. Same as before, Get Data – Blank Query, copy/paste script in Advanced Editor and name it as PowerBI_functionDMV.

    (Port as number) =>
    let
    Source = “localhost:” & Text.From(Port),
    Databases = AnalysisServices.Databases(Source),
    FirstRow = Table.FirstN(Databases,1),
    dbName = Table.Column(FirstRow, “Name”){0},
    Result = AnalysisServices.Database(Source, dbName, [Query=”select [FUNCTION_NAME], [DESCRIPTION], [PARAMETER_LIST], [RETURN_TYPE], [ORIGIN], [LIBRARY_NAME], [INTERFACE_NAME] from $SYSTEM.MDSCHEMA_FUNCTIONS”])
    in
    Result

    And finally, go back to DAX functions query Matt built, and replace first step in the query: instead of ‘= AnalysisServices.Database(…’ write ‘= PowerBI_functionDMV(AnalysisServicesPort)’. Bingo – every time you close/restart PowerBI desktop, your query will dynamically read port number and database name of local SSAS instance. As long as you run it under same user of course…

  9. I love this. I wanted to put some type of visualization that shows the most common DAX functions by usage. I have Googled, and haven’t found anything. Does anyone have a good source of data about DAX usage?

    1. Great idea Jessica – and I say that because once upon a time I had that same idea and actually created my first iteration of this Great Function Project workbook for essentially that purpose.

      I cannot help with a DAX usage data source, but what I did was just dump the list of functions to Excel and arbitrarily assigned a score out of ten to each function then merged the excel source in.

      I train a lot of long time Excel users that aren’t sure they really want to “give up” the time they’ve invested in learning that language. What I wanted to communicate was a) there is a ton of overlap particularly with the core functions, you’re not giving up anything and b) just like Excel, if you can get comfortable using ~10 of the ~126 DAX functions you’re going to be doing great things – you don’t need to learn all of it.

      If you added this as a calculated column and then used this score column as the sizing of the word cloud, it conveys these points. Of course the scores are subjective, but for someone in their first year or two of DAX, this is mostly what I’d expect they’d be using (sort of ignoring the syntax sugar functions like DATEADD or DATESYTD).

      DAX Utility Score =
      SWITCH (
      TRUE (),
      ‘DAX'[Function name] = “CALCULATE”, 10,
      ‘DAX'[Function name] = “FILTER”
      || ‘DAX'[Function name] = “ALL”
      || ‘DAX'[Function name] = “COUNTROWS”
      || ‘DAX'[Function name] = “SUM”, 8,
      ‘DAX'[Function name] = “SUMX”
      || ‘DAX'[Function name] = “DIVIDE”, 7,
      ‘DAX'[Function name] = “SWITCH”
      || ‘DAX'[Function name] = “DISTINCTCOUNT”
      || ‘DAX'[Function name] = “IF”
      || ‘DAX'[Function name] = “OR”
      || ‘DAX'[Function name] = “AND”, 6,
      1
      )

  10. Hi Mat, thank you! Could you please let me know the name of the text visual that is in the lower left corner of your Power BI Report? Thanks

Leave a Comment or Question