Less Commonly Used Advanced Table Functions

One suggested session(s) I would find extremely helpful would be a deep dive on less commonly used table functions that you haven’t addressed previously, such as GENERATE, GENERATEALL, GENERATESERIES, ROW, DETAILROWS, SUBSTITUTEWITHINDEX, KEEPFILTERS, AND DATATABLE.

I’ve looked up in the DAX guide what these functions do, but it would be great to have you present some practical applications and give your thoughts on their overall utility. Basically, I don’t know whether I don’t use these functions because other functions do the same job better (e.g., how EARLIER has largely been driven into retirement by variables), or whether they do have significant utility and I just don’t understand them well enough to take advantage.

Thanks for considering.

  • Brian

@BrianJ
I know you asked Sam, but here’s my take. There are a lot of ways to accomplish a certain task. Many of these functions you listed are great for using DAX as a query language vs. writing measures which is done mostly in DAX studio. I think most people are using either PowerBI or excel as the front end vs writing queries. With that being said:

  • GENERATE, GENERATEALL and GENERATESERIES are table functions. I have found it much better to push this type of thing to Power Query. I’m trying to think where these would be useful in measures themselves, and I cant think of anything right now.

*ROW. I do use this in DAX studio to trouble shoot since you need a table output. But once again, I dont think I have used this in any sort of production environment.

*DETAILROWS. Didnt even know this existed and doesnt show up on the msft dax reference, just the other one. So no idea on that

*SUBSTITUTEWITHINDEX. only useful in writing queries.

*KEEPFILTERS. Now this one that I do use. When used with CALCULATE it says to put the filters into a logical AND instead of OVERWRITE. Certain situations where I have found this useful, but only a handful of times.

*Datatable. This could be used in measures. if you ever seen { “Product 1”, “Product 2”} that’s a data table. Could be useful in certain situations, but if you are going to create a physical table, much better to do in power query.

I think DAX and Power Query have so much to offer that it’s only natural to want to use it all. While it’s great to always want to learn more I always try to keep it in check. You, and by “you” I mean everyone, will never know everything. Just not possible. I would just be aware these functions exists and what they do. Then if you come across a specific situation where they could help, dive in deeper.

Just my take on this topic. I always try to figure out where my time would best be served.

-Nick

Enterprise%20DNA%20Expert%20-%20Small

1 Like

@Nick_M,

Thanks – terrific feedback. I remember reading about the CROSSJOIN function, and thinking it wasn’t that useful for measures, then seeing Sam’s video on it and realizing it actually had a lot of practical utility. Which got me wondering whether any of the others on the list I sent were similarly useful. Sounds like probably not, with the possible exception of KEEPFILTERS.

Really appreciate the detailed response.

  • Brian

@BrianJ

I definitely get where you are coming from. I often have the thought of “wait, is there a better way to do this…” when working on things. But DAX and PQ are great programming languages that offer many ways to author a solution. I do think though that there are patterns that can be used and just tweaked for a specific situation. I do agree on CROSSJOIN, though I’d prefer to do that in Power Query if want a physical table :), but definitely useful in a temporary table.

The nice thing, or at least I think so, is that there never an “end” point in all of this where you say to yourself, “You know, I know everything there is to know.” Just wont happen, which is a good thing. Just got to start with the basics and move up as you get really good with those. I contend that you could do very well with just the basics.

I also want to be clear that those functions are not useless, they have their part for sure. Just a matter what is the correct tool for the job.

Sorry for my rambling :slight_smile: If you come across any specific questions on those functions (or any others really) I’d say post them up and see what the community comes up with.

Nick

Enterprise%20DNA%20Expert%20-%20Small

@Nick_M,

You hit the nail on the head. The “is there a better way?” question is what motivated my original post. Most of the projects I’ve been working on have relatively small fact tables, so performance concerns have not been an issue. I’m writing a lot of DAX that works fine for the projects at hand, but I just want to make sure I’m not falling into ruts or doing things in a way that’s going to come back and bite me when I have a project with a large fact table where performance will be an issue.

In terms of getting to the “end”, in addition to the continual stream of new eDNA videos, I’m working my way through the new version of The Definitive Guide to DAX (that title is a total understatement…) and Gil Raviv’s book on Power Query. Even a few minutes with those quickly makes you realize that ever “getting to the end” is a ridiculous concept, which I agree is one of the things that makes Power BI both compelling and fun.

Thanks again for your perspectives – great stuff.

  • Brian