The DAX Draft

Rob Collie (00:00:00): Hello, friends. I am super, super excited about this episode, because it's a little bit different, and it's a lot of fun. We did a DAX functions draft. What is a DAX functions draft? You might ask. Well, imagine that you had to take turns picking DAX functions against other trained professionals, and every time you pick a function, now your opponents can no longer use that function. Only you can use that DAX function. And of course, when they pick a function, you don't get to use it either. Now, of course, this is a silly game. None of us is going to be actually limited to these functions in real life, but what it does, first of all is a lot of fun. Playing a game like this with the serious contents of your day-to-day business life. I mean, mwah, it's delicious.

(00:00:47): Secondly, it is like a backwards way of ranking the importance of DAX functions, but that leads me to the third thing, to me, it might even be the most compelling, is that you really can't rank these functions necessarily, but exposing the nuances of what functions can do and the personal dialects that evolve in one's usage of DAX. I think that's just, Mm, again, delicious. And the chefs that we brought on for this, Leigh Ann Thibadoux and Ed Hansberry. They did, in fact, serve up a tasty feast. Now I participated as well. It was the three of us, and I basically brought fast food. Well, the other two were artise. We also had Bill Sundwall serving as commentator/referee.

(00:01:31): And what we ended up with might be in fact, well, let's just say it's a candidate for my favorite episode so far. You can't pick. It's like picking your favorite child. And the last thing I want to say is that this conversation captures something important about our culture here at P3 Adaptive. Very serious, very rock solid. We know our stuff, except for me. But we're very human and we have fun with it. As you're listening to this, maybe play a game where you're thinking about what you would pick if you were up instead of me, Leigh Ann, or Ed at each pick. If you have opinions on how we screwed this up or what you would've done differently, hit us up on Twitter. Let us know. Maybe we should set up some Discord or something where we can chat about these sorts of things over time. If you've got ideas there, let us know as well. All right, it's time for me to stop hogging the mic. So let's get into it.

Announcer (00:02:22): Ladies and gentlemen, may I have your attention, please.

(00:02:26): This is the Raw Data by P3 Adaptive Podcast, with your host, Rob Collie and your co-host Thomas LaRock. Find out what the experts at P3 Adaptive can do for your business. Just go to p3adaptive.com. Raw Data by P3 Adaptive is data with the human element.

Rob Collie (00:02:52): All right, so here's the idea. I play a lot of fantasy football, which involves this concept of a draft, and you take turns picking players just like you would picking teams for kickball, which by the way, I was almost always picked last for. So I guess fantasy football is one of my ways of making peace with that whole process. I was listening to a podcast the other day about fantasy football, and they were talking about how they had recently conducted a draft of Christmas songs. So they had taken this draft format that's used for fantasy football, for instance, and applied it to Christmas songs. They took turns drafting Christmas songs. I guess the idea is if I pick that Christmas song, you never get to hear it again, but I get to take it with me forever.

Bill Sundwall (00:03:33): I feel like the more interesting draft would be Christmas songs you never have to hear again.

Rob Collie (00:03:37): Okay, see, we could do that too. Mark that down for future ideas during the off weeks of holiday recording schedule for our podcast, or hell, any podcast for that matter. And so it occurred to me that we could do something nerdy and do that with DAX functions. DAX functions in particular, because I know those, sort of.

Bill Sundwall (00:03:55): Nobody knows M functions.

Rob Collie (00:03:57): Yeah, there could be a Power Query M version of this, but it's not like I'm going to participate in such a thing. Because like what, like List.Generate? All right. I'm out. That's all I got. There's a buffer, right? Is it a List.Buffer? Is that-

Leigh Ann Thibadoux (00:04:08): Table.Buffer.

Rob Collie (00:04:09): Table.Buffer. There you go.

Bill Sundwall (00:04:11): That's Power Query though.

Rob Collie (00:04:12): Yeah, I know. We're not doing that. So that's the thing. I don't know any of that stuff. So we're going to do DAX, and the idea here is that we're going to take turns picking functions. Once you pick a function, you get to use that function in this hypothetical game. Going forward, after the draft, you would be able to use that function whenever you want, but the other players in the game are never allowed to use it again, and you still have to make do as a professional. Again, this is hypothetical. When this is over, Ed and Leigh Ann, I completely want you to be able to use all the DAX functions in your daily lives, and I'm going to do the same. But to make this game work, that's what we're buying into, is this fiction. And the way that these drafts work, they follow what we call a snake format. Basically, the order of the draft reverses each round. So I've decided ahead of time that I get to pick first.

Bill Sundwall (00:05:00): Rigged.

Rob Collie (00:05:01): It's my show, and I have no idea. Remember, I'm really rusty on DAX, and I'm really rusty on DAX relative to a version of DAX that's very, very, very old and outdated. I'm going to end up drafting functions in this draft that I don't even know what they do. I'm just going to be guessing that that's something that'll be useful. And, Ed being the ringer of DAX, we're going to make him go last. So it's going to go me, then Leigh Ann, then Ed. And then the second round, it'll reverse, and so that'll be Ed, Leigh Ann, then me. Ed will always pick twice in a row, and I will always pick twice in a row after the first pick. And Leigh Ann, you're always going to have a couple picks between your picks. You have a little bit more think time. Let's introduce ourselves, shall we? So Leanne, this is your first time on the show with us.

Leigh Ann Thibadoux (00:05:44): Yes.

Rob Collie (00:05:44): Right. Introduce yourself. Tell us a little bit about yourself so that people know who's competing.

Leigh Ann Thibadoux (00:05:49): Okay. My name is Leigh Ann Thibadoux, and I have been working with Power BI for a little over three years now. Just joined P3 a few months ago in October, and I live in West Virginia, Morgantown. I really enjoy mountain biking, and I'm a very average middle-of-the-road DAX user, so I'm expecting to get totally cream.

Rob Collie (00:06:16): There's the impostor syndrome. Look, you're not average DAX person.

Leigh Ann Thibadoux (00:06:20): Well, I'm the one that has to look a lot of stuff up.

Rob Collie (00:06:23): Well, that's fine. Trust me. You're better at DAX than I am, and all I ever did, Leigh Ann, was write the number one selling book on DAX in its day, and you're better than me. Now I know that Ed is better than me, without a doubt, Ed waxes philosophical, intellectual, on Twitter, about DAX and functions, and how they should work, and how they don't work. We know what we're up against, Leigh Ann.

Leigh Ann Thibadoux (00:06:47): Well, when I actually agreed to do this, when you sent out the request, I thought, "Well, okay. As long as Ed is not doing it, I'll be fine."

Ed Hansberry (00:06:57): Wow, the amount of pressure here.

Rob Collie (00:07:00): So, Ed. You're known in the community, but introduce yourself anyway.

Ed Hansberry (00:07:03): My name is Ed Hansberry, and I've been with P3 now for about a year and a half, a little over a year and a half. And I'm an associate director, so I still spend most of my day doing consulting work, but I do help out on some of the tasks within P3, keeping things straight, helping onboard people, that type of stuff.

Rob Collie (00:07:21): And now, doing DAX drafts.

Ed Hansberry (00:07:23): Yes, I'll add that to my resume or list of accomplishments.

Rob Collie (00:07:26): And we needed a referee/commentator. I knew who I wanted for that.

Bill Sundwall (00:07:31): But they were unavailable, so...

Rob Collie (00:07:32): So we fell back to Bill. Bill introduced yourself as well, please.

Bill Sundwall (00:07:36): Bill Sundwall, director of client services at P3, which means I do even less frontline stuff, which makes me a curious choice for this. However, Rob and I have talked a lot, and Rob knows that I get my opinions wholesale, so the cogs on this for me is not going to be huge.

Rob Collie (00:07:51): Yeah, this costs us nothing. Bill's just a solid co-conspirator for me in anything silly.

Bill Sundwall (00:07:59): Great of developing a reputation.

Rob Collie (00:08:01): This is a dumb idea, right?

Bill Sundwall (00:08:03): Better get hell involved.

Rob Collie (00:08:05): Capital D, capital I, dumb idea, but it's also a fun idea.

Leigh Ann Thibadoux (00:08:09): I thought we would actually be solving DAX problems and like-

Rob Collie (00:08:15): Oh, God. No.

Leigh Ann Thibadoux (00:08:15): ... we would get a problem, but then you would get functions taken away, and so you would have to replace them with other functions. That's what I thought we were doing. I was like, "Keep doing it."

Rob Collie (00:08:23): Okay, okay.

Bill Sundwall (00:08:23): That sounds suspiciously like effort.

Rob Collie (00:08:25): That would be the way to do it, right? But remember, we have a zero-prep policy here.

Leigh Ann Thibadoux (00:08:30): And that's exactly what I've done, zero prep.

Rob Collie (00:08:33): Perfect. You're a natural. Same here. Hypothetically, in your head, we should be thinking about such things after Rob just took that function away from me when he picked it. How would I get by without that? What alternate methods could I use? And this is something that Bill's going to help us with a little bit commentating as we go. All right. So, couple of quick rules. Number one, I decided up front that no one can draft calculate.

Leigh Ann Thibadoux (00:08:56): That was my next question.

Rob Collie (00:08:57): It's a reserve function we all get to use. If it were draftable, I would pick it first, and then sit back and laugh. I think maybe one person could cobble together a strategy without ever using calculate, but I'm not sure that two people could. So let's just take that off the list. I think that's really it. In later rounds, maybe someone will get dirty and start drafting the basic arithmetic functions. We'll see. We'll see how this goes. Shall we begin?

Leigh Ann Thibadoux (00:09:22): Let's do it.

Bill Sundwall (00:09:23): Just to clarify, we're doing eight rounds?

Rob Collie (00:09:25): At most, eight rounds, Bill. I'm going to keep track of this on just pen and paper. Now again, Leigh Ann, if we had prep for this, we'd have an app. There'd be like a power app for this, and the list of functions would be being whittled down in real time as people took them, and you get a real time readout of what you've got in your portfolio, and what the other people have, and all of that. But no, so I think we should all just keep track maybe on pen and paper or something in front of us.

Bill Sundwall (00:09:54): Perhaps even a spreadsheet.

Rob Collie (00:09:56): Maybe even a spreadsheet. But the thing is I've got too many computers open, Bill. I've got this one that I'm talking to you all on. By the way, if you screw up and you navigate this browser tab to go look up a DAX function, you're going to be off the recording. That happens at least once every four recordings is someone goes and look something up. So don't do that. So those are the rules. We're going to do eight rounds like that, and so it'll be 24 functions total. They're going to be taken off of the list, and all the remaining functions that aren't drafted, those are going to be available to anyone. So an undrafted function at the end of this exercise is still open for use by everyone.

(00:10:29): It's only the 24 functions that we're going to be grabbing exclusive access to. I'm looking forward to this because Leigh Ann and Ed, and Bill, all of you are super, super, super good at this stuff, and you're in the trenches every day, and we're going to learn some things in the process, and we're even probably going to learn some things from each other. So I'm going to go first, ready? With the first pick in the first ever DAX functions draft, I, Rob, select the FILTER function. I can do a lot with the FILTER function. Like Archimedes once said, give me a place to stand and the FILTER function, and I will move the world.

Leigh Ann Thibadoux (00:11:06): I mean, really, you're not supposed to use the FILTER function and calculate anymore, is that right? See, I'm looking at Ed through the camera.

Rob Collie (00:11:12): You're trying to read Ed's facial expressions.

Ed Hansberry (00:11:14): Yeah, no. You just don't use it to filter an entire fact table. There's a lot of reasons to use it, and taking filter away took my third pick. So, no. FILTER's a great function.

Rob Collie (00:11:25): Oh, see?

Ed Hansberry (00:11:27): I just don't use it for the fact table.

Rob Collie (00:11:28): Ed's a natural at fantasy football drafting. You can just tell right there. That is a dynamic that happens in every fantasy football draft. Someone picks something and someone else goes, "Oh my gosh, I have to look all the way down to page two of my cheat sheet to find this person, but I can't believe what a newbie you are for taking..." This is already going 100% according to plan. All right, I got the FILTER function.

Leigh Ann Thibadoux (00:11:55): So I am going to pick USERELATIONSHIP.

Rob Collie (00:12:01): USERELATIONSHIP. Oh, no. Yeah, that's a good one.

Leigh Ann Thibadoux (00:12:04): Ed's not terrified.

Rob Collie (00:12:05): What...

Bill Sundwall (00:12:07): I think this is where things have immediately started to get interesting, because when it comes to FILTER, it significantly complicates everyone's life to have to work around it. But Leigh Ann has just taken a big chunk of the possibility space of Power BI and just carved it off for her own.

Rob Collie (00:12:23): Yeah, yeah. I almost never end up with a model anymore that doesn't have at least one inactive relationship.

Leigh Ann Thibadoux (00:12:29): Right. I'm like, "Is it just me? Am I very mediocre at these models?

Rob Collie (00:12:34): The early versions of DAX didn't have USERELATIONSHIP. We-

Bill Sundwall (00:12:36): Really?

Rob Collie (00:12:37): Back in my day, you had one kind of relationship, and there was only one relationship possible between two tables and that was it, and it sucked.

Bill Sundwall (00:12:46): I think where this really gets compelling is if it were any other kind of relationship, you can still mess around and do TREATAS, or CROSSFILTER, or what have you. But where this gets sticky is multiple date relationships, because there's that little extra layer in the way date tables fire. I'm not saying it's impossible, but the level of headache. Leigh Ann, this isn't-

Rob Collie (00:13:08): Yeah, yeah.

Leigh Ann Thibadoux (00:13:08): Basically, all my models break down if I don't get to use that.

Rob Collie (00:13:11): Well, I've got the FILTER function, and so I'm just not going to create relationships between tables.

Bill Sundwall (00:13:20): Oh, so you're in client?

Rob Collie (00:13:20): Yeah, I was just going to make it all big one flat Frankentable.

Bill Sundwall (00:13:22): Nothing could go wrong according to Microsoft's documentation, right?

Rob Collie (00:13:27): That's right. You've got to lie to people to tell the truth. No, no, it's easy. One big foyer table and then let them discover after they've already got committed. So that's a surprise pick, by the way. USERELATIONSHIP. No one saw that coming. I like it. I like it a lot. You force your opponents to respond to you. You go on the initiative. Ed, you get to go with two consecutive picks.

Ed Hansberry (00:13:46): All right, so I'll just say them both at the same time. So I'm going to go with SWITCH and COUNTROWS.

Rob Collie (00:13:53): Oh, COUNTROWS. I was really hoping that COUNTROWS was going to make it back to me.

Leigh Ann Thibadoux (00:13:58): See, I think... Okay, without SWITCH, because I could use the IF function, if you had to. I mean it would be very inefficient and awful, but you could still.

Rob Collie (00:14:06): What if I take the IF function now that SWITCH is gone, right?

Bill Sundwall (00:14:10): I mean, Leigh Ann has a pick before you do.

Rob Collie (00:14:12): I know she does. So-

Leigh Ann Thibadoux (00:14:17): Oh, that would be too easy if I did that. Now I can't do it. Now I can't do the IF function, now that I've thrown it out there.

Rob Collie (00:14:18): Do you think I'm going to be nice on the way back and not take IF, I mean I get two picks before you pick again.

Ed Hansberry (00:14:23): Technically, nobody had to get SWITCH or IF, because there's also IF.EAGER, which operates as an IF. So technically, all three would work. It's just the code is just a headache.

Leigh Ann Thibadoux (00:14:33): Wait, there's a what?

Bill Sundwall (00:14:35): IF.EAGER is the least efficient, because it's eager. So it's going to evaluate every branch of it.

Ed Hansberry (00:14:39): True, but it works. It's not a roadblock.

Rob Collie (00:14:41): So in my day, we didn't have IF.EAGER. All we had was IF, and I thought IF was always evaluating every branch. Is that no longer true?

Bill Sundwall (00:14:49): Didn't it use to, and then they changed the way it worked to enable lazy evaluation that nobody were like, "We need a callout for eager evaluation for the extremely corner cases where you need that.

Rob Collie (00:15:02): I don't even understand. So this is all just performance stuff, right?

Bill Sundwall (00:15:06): Yeah.

Rob Collie (00:15:07): Oh, goodness.

Ed Hansberry (00:15:08): Yeah, we're assuming this is a billion record model, so it needs to be performant.

Bill Sundwall (00:15:10): Yeah.

Rob Collie (00:15:10): All right. So you've got SWITCH, Leigh Ann, it sounds like IF and IF.EAGER are available to us. Ed could grab one of those at some point and create additional scarcity in the conditional branching space, and absolutely screw one of us. Maybe we have to respond. Maybe we have to protect ourselves with IF and IF.EAGER. I don't know. Anyway, it's your pick. I shouldn't be talking so much when it's not my turn.

Leigh Ann Thibadoux (00:15:34): Wait, it's my turn.

Rob Collie (00:15:38): Yeah.

Leigh Ann Thibadoux (00:15:41): Oh, man. Now I feel like I have to pick the IF function.

Bill Sundwall (00:15:42): Here's the thing. Not so much as a fantasy football player, but as a player of draft-based games, generally, hate drafting always feels really good, but it is rarely an effective strategy. So-

Rob Collie (00:15:53): Hate drafting.

Leigh Ann Thibadoux (00:15:54): Hate drafting.

Rob Collie (00:15:55): Hate drafting. I've never heard this.

Bill Sundwall (00:15:56): That's where you sacrifice your pick specifically to screw with an opponent.

Leigh Ann Thibadoux (00:15:59): Okay, I am going to pick... I already know which one. I'm going to pick SELECTEDVALUE.

Bill Sundwall (00:16:03): Oh.

Rob Collie (00:16:03): SELECTEDVALUE.

Ed Hansberry (00:16:08): That hurts.

Rob Collie (00:16:09): Okay. So this hurts me and doesn't hurt me. It doesn't hurt me for the same exact reason is that I've never used it. When, Leigh Ann, you're taking it in the second round. It's a premium pick, and Ed is going, "Oh, you sunk my battleship." And I'm going, "I don't get it." I'm not even in on the joke.

Bill Sundwall (00:16:30): Okay, this is a very powerful move. One, it's effective just from a doing-stuff-perspective. All the cool visual tricks in the world eventually rely on SELECTEDVALUE along with other things. The other thing is we're talking about scarcity a lot here. SELECTEDVALUE is technically syntax sugar, but it's syntax sugar for two functions. It's syntax sugar for IF HASONEVALUE. So now, for the other two of you to maintain parity, either one person is out, and the other person has to blow two picks to Leigh Ann's one, or you guys end up split across it and you both lose out.

Rob Collie (00:17:09): Damn, that's-

Bill Sundwall (00:17:09): And someone picked HASONEVALUE.

Rob Collie (00:17:12): This is top-notch commentary.

Leigh Ann Thibadoux (00:17:13): There's a replacement that HASONEVALUE you could use instead of selected val... I actually thought about that the other day. I was like, "Ah, SELECTEDVALUE. But, man. I just use it so much."

Bill Sundwall (00:17:25): Yeah.

Rob Collie (00:17:25): I love this. I love it so much.

Ed Hansberry (00:17:33): Oh, I don't need HASONEVALUE. I have COUNTROWS. I could use COUNTROWS instead of HASONEVALUE. So if COUNTROWS the VALUES equals one, then I'm good.

Rob Collie (00:17:39): Let me give you some more information to chew on Ed as I take the ALL function and the-

Ed Hansberry (00:17:46): Oh, that's a good one.

Rob Collie (00:17:46): ... and the VALUES function.

Leigh Ann Thibadoux (00:17:49): Yeah, I would be pretty crippled without the VALUES function.

Rob Collie (00:17:52): Well, you got the SELECTEDVALUES function, which apparently is magic.

Leigh Ann Thibadoux (00:17:58): Right, it is.

Rob Collie (00:17:58): So this, by the way, confirms for me, Leigh Ann, that at the beginning you saying, "Oh, I'm pretty average at DAX." If you're picking a function that I've never used in the second round, and Ed is upset about it, I rest my case.

Leigh Ann Thibadoux (00:18:11): All of my measures are different combinations of basically five functions. I'm either too lazy or too busy to learn how to do other functions. I'm just like, "No, I can do it with SELECTEDVALUE. I just need to think about it."

Rob Collie (00:18:24): I'm the same way, but I've got half the dialect. I've got half the vocabulary.

Bill Sundwall (00:18:28): So I find this to be real interesting picks on the wheel, because this usually comes up teaching advanced DAX. It's not in the curriculum, but it's one of the little things they sprinkle in, is to flip a two by two grid up there and say, "Hey, there's this family of functions that you need to know and understand all the cousins, even though you're only going to use half the family most of the time, that being ALL and VALUES." But the other half, again, we find ourselves in a situation where if somebody really needs these functionalities, they could get there, because ALLNOBLANKROW and DISTINCT have, from filter context perspective, they do the same thing as ALL and VALUES respectively.

(00:19:06): But just like it says on the 10, ALL and VALUES include a blank row to maintain referential integrity. So a person could, if they were dumb and wanted to blow multiple picks on maintaining that functionality, you could take... We're going to call the table constructor off limits here, so you don't need BLANK to force a blank row, but you would then need UNION to union a blank row onto the result of ALLNOBLANKROW or DISTINCT to replicate ALL or VALUES.

Rob Collie (00:19:38): Did anyone understand any of that?

Leigh Ann Thibadoux (00:19:39): You had me for the first good half of that, and then I trailed off there.

Rob Collie (00:19:44): That's half more than me. This is one of those cases where I wish it was a video, because then you could just see this look of dumbfounded confusion on my face. If he was making all that up, I wouldn't have been able to tell. Well, Leigh Ann, it's your pick.

Bill Sundwall (00:19:57): Well, I was relying on Ed there to tell me if that's not actually possible, but I think it should be.

Ed Hansberry (00:20:02): Oh, it is. Yeah.

Leigh Ann Thibadoux (00:20:04): So we're only picking DAX functions.

Rob Collie (00:20:06): What else are you thinking? I don't want to miss a good idea, and an opportunity to change the rules midstream would really appeal to me.

Leigh Ann Thibadoux (00:20:12): I don't know if it's necessarily on or in-game, but I would pick VAR.

Rob Collie (00:20:19): Oh.

Ed Hansberry (00:20:19): Oh, yeah. That's a statement.

Rob Collie (00:20:21): You know what? I'm going to allow it.

Leigh Ann Thibadoux (00:20:23): Whoo.

Ed Hansberry (00:20:25): Well, then that puts EARLIER and EARLIEST back in play, which nobody should be using.

Rob Collie (00:20:30): No, you know what? I'm not the ref. What am I doing? Bill-

Bill Sundwall (00:20:33): And so-

Rob Collie (00:20:34): ... you're in charge of this game.

Bill Sundwall (00:20:36): ... my inclination is to not allow it, because now we're talking about... While that's technically written in DAX, one, it's not a function. Two, what other elements of things that are tabular modeling, but you might perform DAX over them, but aren't explicitly DAX. Someone could claim one-to-many relationships and the game's over.

Rob Collie (00:20:56): Calculation groups.

Leigh Ann Thibadoux (00:20:59): True, true, true. Calculation groups, yeah.

Bill Sundwall (00:21:01): Mm-hmm.

Leigh Ann Thibadoux (00:21:01): Yeah.

Rob Collie (00:21:01): All right, all right, all right. VAR and RETURN are even more gray than those, because they appear within a DAX formula.

Bill Sundwall (00:21:09): USERELATIONSHIP operates over a relationship, so you can't-

Rob Collie (00:21:13): Admit a compelling case, which is that now we had to use EARLIER. Ain't nobody got time for that.

Leigh Ann Thibadoux (00:21:18): Yeah, I had to test the water. I had to throw it out there and see.

Rob Collie (00:21:22): I respect that fence testing. Leigh Ann, you're a solid choice for this. My respect for you grows. All right, so we're not doing VAR. By the way, we're in the middle of the third round here.

Leigh Ann Thibadoux (00:21:31): So I would pick just something simple, SUMX.

Rob Collie (00:21:35): Yeah, I knew that was coming sooner or later.

Leigh Ann Thibadoux (00:21:36): Just simple and boring, but good workhorse.

Rob Collie (00:21:38): Not simple and boring. And I don't know what the hell I'm going to do without it, to be perfectly honest. Ed will find 18 ways perhaps to get around it, but I will not.

Leigh Ann Thibadoux (00:21:47): SUMX is like, for me, the quintessential formula that differentiates DAX from Excel. The ability to do that in one function without having a helper column. When I've taught people DAX, I'm like, "This is why it's cool. Look at this. SUMX, I got the power."

Rob Collie (00:22:07): Funny story about SUMX is that I was asked a question when I was working at this startup pivot stream in 2010. Can we do this in Power Pivot, was the question, and it was basically asking like, "Can you sum it up like that?" And I said, "No, DAX doesn't have that." On a hunch, I'm like, "Oh, maybe that's wrong." And I went looking, and that's when I found SUMX. I didn't know that SUMX was in there or why I would use it, and then I had to go back to them six hours later and say, "Ah, I was wrong." DAX did in fact anticipate this problem, and it was a very exciting moment. And then I wrote the Five Point Palm Exploding Function Technique blog post.

Leigh Ann Thibadoux (00:22:45): Okay, I'm going to have to read that.

Rob Collie (00:22:45): It's like I had discovered the fire. But this is a function that someone else had known was needed and had put it in the product. So clearly, that was the discovery.

Leigh Ann Thibadoux (00:22:53): That's what I thought when I saw SUMX.

Rob Collie (00:22:54): Yeah.

Leigh Ann Thibadoux (00:22:58): I'm sure there are many workarounds to it though, that I don't know about.

Rob Collie (00:23:00): Me too, but I don't know what they are. So I'm screwed. Ed, you're up for two consecutive picks.

Ed Hansberry (00:23:06): All right. So my first one, I'm going to pick AVERAGEX, because I think between that and my COUNTROWS pick, I can rebuild the SUMX.

Leigh Ann Thibadoux (00:23:12): Oh, man.

Rob Collie (00:23:13): Okay.

Ed Hansberry (00:23:14): Some math in there, but at least I'm not out of the game. And then I'm going to take SUMMARIZE. Now I don't need VALUES anymore because now I can do it over one or multiple columns.

Rob Collie (00:23:24): SUMMARIZE is fine. I don't care. Still never used it.

Leigh Ann Thibadoux (00:23:27): I thought SUMMARIZE was not best practice anymore.

Ed Hansberry (00:23:31): No.

Bill Sundwall (00:23:31): Don't add columns via SUMMARIZE. Use SUMMARIZE to summarize, but don't use it to add columns.

Leigh Ann Thibadoux (00:23:36): Okay, that's it. Yeah. So that's why I always use SUMMARIZECOLUMNS, and I just probably gave away my next pick.

Rob Collie (00:23:43): Well, it's okay. It's your pick. No one can get ahead of you. It is your turn, yeah.

Leigh Ann Thibadoux (00:23:47): Okay. I'm probably going to pick SUMMARIZECOLUMNS, then I'm just going to scoop that right up.

Rob Collie (00:23:49): For a moment there I got really excited, Leigh Ann, when you said, "I thought SUMMARIZE was no longer best practice," and I'm like, "Aha." By sitting still and stubbornly refusing to use the function I have become current again. But then when you said SUMMARIZECOLUMNS. I'm like, "Oh, well."

Leigh Ann Thibadoux (00:24:04): If I read a post that says, ""Oh, if you use this function in this way, you could get an error. So if you don't want to get that error, use this other function." I'll just always use that other function. I'm like, "Oh, no. It's always SUMMARIZECOLUMNS now. Never SUMMARIZE, because chances are I'll totally use it wrong."

Bill Sundwall (00:24:19): So that's interesting to me though, because isn't SUMMARIZECOLUMNS the one that will not respect an outer filter context?

Ed Hansberry (00:24:28): It doesn't respect evaluation context. So it works in a card, it works in queries. It's awesome in queries. It doesn't really work in other visuals like bar charts. You'll just get an error in the model. It won't even give you bad values. You just get a gray visual.

Bill Sundwall (00:24:46): This is hearsay, but if you ever get into performance tuning, basically, every query you get from a visual when you run the performance analyzer relies on a SUMMARIZECOLUMNS. My high-level suspicion is, technically, that's an internal thing that the team just uses to make the visuals query the model. They can't hide it from us, but they can't make it work in the way we think it ought to and still do what they need to do with it.

Leigh Ann Thibadoux (00:25:15): I have noticed that all of the queries use SUMMARIZECOLUMNS, that is very odd that you can't use the function in a bar chart. I feel like now I need to test this out. Now after we've had this conversation.

Rob Collie (00:25:30): I just want to jump in and say, "I love you all. This is so great." It's like exactly what I hoped for, is to feel really, really, really bad at DAX.

Bill Sundwall (00:25:48): You've made a pretty solid set of picks so far.

Rob Collie (00:25:50): Well, I got to go first. That helps. All right, so I'm coming back. Everyone's got an X-function. Ed's got COUNTROWS. I'm going to grab COUNTX, because I think that between COUNTX and FILTER, and VALUES, and ALL, I think I can probably cobble together the equivalent of a COUNTROWS, and plus, I can't let you all be the only ones with X-functions. Mm-mm, not about to out X-function me. Okay. So, COUNTX because I don't know how to do all these fancy things you all been talking about. I'm going to do the HASONEVALUE function. I need it.

(00:26:25): This also, by the way, in my experience, because I'm primitive. I'm a troglodyte. HASONEVALUE, if I am allowed to pick up an IF or an IF.EAGER at some point. HASONEVALUE allows me to use the VALUES function as a literal sometimes. I can protect the VALUES function from being treated as a table provided instead of a single value. I can use the VALUES function as a single value as long as I have an IF HASONEVALUE protecting it. I know that's really, really, really cloogy and primitive, and the equivalent of writing DAX in an alley. That's me. That's me.

Bill Sundwall (00:26:59): That's important enough syntax, they sugared it up into SELECTEDVALUE.

Rob Collie (00:27:04): Oh, is that what that's for? Son of a bitch. Oh, that's great.

Bill Sundwall (00:27:11): Hi, Rob. Welcome to 2015.

Rob Collie (00:27:16): 2015. It took me a moment for the second stage of that joke to hit me. Subsequent processing went off and the 15th landed. I'm like...

Bill Sundwall (00:27:24): To be clear, my trivia brain is not so great that I may have the year wrong there, but I think-

Rob Collie (00:27:29): It doesn't matter. It was devastating.

Ed Hansberry (00:27:32): It's pretty close. It was one of the last functions created that never made it into Power Pivot. So you still have to use the old IF HASONEVALUE VALUES.

Bill Sundwall (00:27:42): Although they're trying to bring that to parity, aren't they?

Ed Hansberry (00:27:45): I don't know. They've added a bunch of new measures, but who knows. When it's added, I'll believe it.

Bill Sundwall (00:27:50): Ooh, shots fired.

Rob Collie (00:27:53): I like it. I like it.

Bill Sundwall (00:27:53): Leigh Ann, it's back to you again.

Leigh Ann Thibadoux (00:27:55): What did people pick so far? Okay.

Rob Collie (00:27:57): I'll give you the rundown. We went FILTER, USERELATIONSHIP, SWITCH, COUNTROWS, SELECTEDVALUES, ALL, VALUES, SUMX, AVERAGEX, SUMMARIZE, SUMMARIZECOLUMNS, COUNTX, AND HASONEVALUE.

Leigh Ann Thibadoux (00:28:19): Wow. Okay. So I'm just really going to start reaching here and get really exotic, because I feel like, for me, that that's covered probably 90% of all the DAX I've ever done. So I'm going to take CONCATENATEX.

Rob Collie (00:28:35): Oh, oh, my emotional favorite. I thought I was going to be able to sneak that in as a latecomer.

Leigh Ann Thibadoux (00:28:39): Just because there's a few problems I've only ever been able to solve, and I still could not solve today without that function.

Rob Collie (00:28:45): Same here. And by the way, I think that I might have had a hand in inventing that function.

Leigh Ann Thibadoux (00:28:50): Oh, wow.

Rob Collie (00:28:52): Seriously. Tim Rodman and I were sitting at a bar in Cleveland one night, and CONCATENATEX didn't exist, and we were talking about exactly one of these problems that you can't solve, and I said, "Oh my God, we need CONCATENATEX." Later that night, I emailed it to the DAX gods at Microsoft. I still knew them at that point, and I never heard back. At some point later, CONCATENATEX showed up. It might have been that they already knew they needed this.

Leigh Ann Thibadoux (00:29:16): It sounds very plausible that you came up with it, and I can't believe you didn't pick it then.

Rob Collie (00:29:21): Well, I had other things I needed to do. I needed my COUNTX and my HASONEVALUE. So, curses. Well played. Well played.

Bill Sundwall (00:29:29): I think there's something to be mentioned here also from a quality of life perspective. As a developer here, CONCATENATEX, this may be my old school take from a debugging perspective, is the best tool in the toolbox. Little less so now that performance analyzer exists and you can just yank the queries out, and all of that. But time was, if you wanted to examine the filter context in a visual, CONCATENATEX was the first thing to reach for.

Rob Collie (00:29:59): Oh my God, that never occurred to me.

Leigh Ann Thibadoux (00:30:03): I feel like I learned something here today.

Rob Collie (00:30:04): Yeah.

Leigh Ann Thibadoux (00:30:05): I was going to say I solved this problem exporting a table to Excel, and whenever I export, it doesn't wrap the column. The column was huge. It was multiple notes that I'd mesh together in one column that they had to be able to export and look at. But if you create a column with CONCATENATEX, you can export it and it keeps the wrap. It keeps the formatting that you put because you've written the formatting into DAX. You've told it, "Okay, go to a next line in DAX," when you use CONCATENATEX. And so it keeps that when you export it, and it's just really cool. I mean, yeah, you can't do that with anything else that I know of.

Rob Collie (00:30:45): That's just your average DAX user nature is just really shining through at this moment, Leigh Ann, when you're talking. What the hell? What did you just say?

Ed Hansberry (00:30:58): Oh, I get completely what she's saying. I use CONCATENATEX to talk to Power Automate, because I can control the formatting as the data comes out of Power BI. And you can't do that with any other function easily. So, that one hurts.

Bill Sundwall (00:31:11): It's the perfect overlap of iterator, and that gives you formatting control.

Rob Collie (00:31:16): For the people who are listening to this. Some of them are just really digging it right now, and just playing along. Like, "Yeah. Oh, wow. Look at that. Oh." They're just like three of you. But then there's some that are listening that are more like me, and I want to just tell the people who are like me listening to this that it's okay. They're there. It's okay. You can still get by with my level of knowledge and skill. The ceiling for you is that you can write a bestselling DAX book. But you also, over time, can grow into understanding some of these things that are going over your head, even I would slowly absorb some of this.

(00:31:54): I probably will start using CONCATENATEX now, occasionally, to debug things, and not just COUNTROWS of the table to see how many rows survive the filtering, which is one of my old tricks. Because it's always either a really big number or one, or blank. That's always the thing. That's the first branch in the decision tree is like, "Did I wipe out all the rows? Am I down to one? Do I have all of them?" Because you're very rarely have seven. Anyway, it's okay to be Rob-level bad. You got to go through that point to get wherever you're going. All right.

Bill Sundwall (00:32:28): So can I attempt to give you the galaxy-brain then on what you were saying in terms of CONCATENATEX.

Rob Collie (00:32:34): Sure. Give me the galaxy-brain.

Bill Sundwall (00:32:36): Let's say your visual has a certain level of a hierarchy, but you are iterating a lower level of that hierarchy at which level you perform the calculation. So COUNTROWS is still good because it'll tell you, essentially, how many members you have in that level of the hierarchy. But if you need to see specifically which ones, because you're SUMXing some calculation that happens at the county level, and you need to make sure in the row for Missouri, am I getting all the counties in Missouri? That's where CONCATENATEX starts to come into it.

Rob Collie (00:33:10): By the way, the use of the term galaxy-brain, the only other place I've ever heard this is on Fantasy Football Podcast. We're like right in the zone. Bill, why aren't you playing fantasy football with us?

Bill Sundwall (00:33:22): Smoke bomb.

Rob Collie (00:33:22): Smoke bomb, ninja dust, I'm out. Yeah, okay. We're going to be up to three leagues at P3 next year. Two beginner humane leagues, and then the one Premier League with a relegation and promotion system. So there's a very easy on-ramp lower level leagues.

Leigh Ann Thibadoux (00:33:40): Is this NFL or college?

Rob Collie (00:33:43): NFL.

Leigh Ann Thibadoux (00:33:43): I don't know anything about NFL.

Rob Collie (00:33:45): NFL. If it had been college, you would have?

Leigh Ann Thibadoux (00:33:46): Only if it was specifically for the SEC.

Rob Collie (00:33:50): Well, that's what I grew up with.

Leigh Ann Thibadoux (00:33:51): It would be a very specific niche of nancy football drafting.

Rob Collie (00:33:54): Man, you could totally play, and you'd be good. What we're doing here is exactly fantasy football. It's dealing with scarcity, balancing off in your portfolio, and people publish before the season. They publish predictions of the relative values of players, so you don't have to even know the players at the beginning of the year. Your knowledge of the teams and the players in the SEC, it doesn't matter. There's cheat sheets that give you that crutch. And I use those cheat sheets too. It's not like I've been driving around the country going to NFL practices, watching these people with a clipboard going, "Mm. I like that cut." I couldn't tell the difference. They all look amazing to me.

Bill Sundwall (00:34:32): Appreciate how in the five-minute break here, Ed has not stopped writing the entire time.

Rob Collie (00:34:37): He's plotting his next 17 moves.

Ed Hansberry (00:34:40): I'm going through my list.

Rob Collie (00:34:41): He's doing this depth-first search of, "Okay, if Leigh Ann takes this, then the Rob does this, and then..." All right, Ed. You're up.

Ed Hansberry (00:34:48): So for my next one, I'm going to take SELECTEDMEASURE. That means I can capitalize on all calculation groups, and because Leigh Ann took CONCATENATEX, I'm going to go ahead and grab EVALUATEANDLOG so I can do my debugging, because that is the ultimate debugging function. If this podcast were four months ago or five months ago, I would've been stuck. But now that function's available.

Rob Collie (00:35:14): I feel much the same, yeah. Should've been four or five months ago, I'd been stuck. I'm also stuck now, but I would've been stuck then too.

Leigh Ann Thibadoux (00:35:23): That SELECTEDMEASURE, that hurts.

Bill Sundwall (00:35:24): Goes to the conversation earlier, we disallowed VAR. This is essentially the same move we ended with USERELATIONSHIP, just like cutting off 200, 300 level applications of the tool. Wow.

Rob Collie (00:35:36): Yeah, yeah. So you use a DAX function as a means of grabbing some additional functionality that would not be draftable on its own. Now I'm thinking about things like selected user or current user, or whatever that function is, or whatever. Is there anything related to row-level security that I could steal with the function? It's Leigh Ann's turn, but-

Bill Sundwall (00:35:54): That's a very interesting question you have there.

Rob Collie (00:35:59): I've never used role-level security, so I feel like it would really pissed everybody else off if I stole it. What's that called again, hate drafting?

Bill Sundwall (00:36:09): Uh-huh.

Rob Collie (00:36:10): Drafting something that you yourself have never even used, because you know the others will be upset.

Bill Sundwall (00:36:17): So it really comes up, my drafting experience has a lot more to do with Magic: The Gathering than it does football. Magic is played in five colors, and sometimes at a draft it is rarely the right option. But occasionally, you will have a sense of what colors another player at the table is in, and you will take a very powerful card in colors that you cannot play with the rest of the deck you were assembling, simply to deny them that card, and that's hate drafting.

Leigh Ann Thibadoux (00:36:43): Or it's a bad strategy.

Bill Sundwall (00:36:45): There are a plethora of articles on the internet about how nine times out of 10 or more, it's just a warm feeling for yourself. It does not actually accomplish the value over replacement. Basically, the card you could have taken and actually used is much better than the one game you might lose to the ridiculous ball.

Rob Collie (00:37:04): So here's the way I would explain that in my experience, is that the amount of harm you can cause to everyone else, if you add that harm up across all of your opponents, that negative value damage in total is greater than the positive value you could have provided yourself with a positive pick for yourself. However, damaging everyone else and adding it up doesn't increase your chances of winning as much as have taken that plus for yourself.

Leigh Ann Thibadoux (00:37:36): Good life lesson.

Rob Collie (00:37:37): You are intuitively correct about something when you make that move. That pick is having more impact overall, the negative pick than a positive pick would've had. So your internal calculus is right about something, but you didn't follow it to its conclusion. You needed that next level, which is okay, but which one increases my chances of winning more, and it's the positive self-pick. Now in fantasy football, there is something that's related to hate drafting that actually does work out sometimes, which is trying to be the first one into a particular category. Everyone's been so far in the draft, let's say, taking running backs and wide receivers. If you're the first person to take a tight end, for instance, or a quarterback, not only do you get the, quote, unquote, "best asset" at that position. If you do it just right, you will trigger everyone else to make a run on that position.

(00:38:27): Now everyone's taken that position, and when it gets back to you, the very next player that you would've taken another position is there anyway. And so you get both. And so then it becomes two positive picks while also inflicting behaviors on everyone else, and that's mastery. And, yes, that is something I try to do all the time. All right, Ed. Again, I'm left with this feeling of fantastic. Ed took two functions that I've never even heard of. This doesn't impact my ability to do any work whatsoever. It certainly sounds like Ed's very happy about having these things, but seriously, I'm aware of calculation groups, but I've never used them. So, yeah. I'm just going to be back to my copy-paste. I was fine with that back in the '80s, apparently, when I was writing DAX, and I'll be fine with it now.

Bill Sundwall (00:39:13): Here's the problem, is Ed also took SWITCH. So he is the master of all he surveys as far as user-driven dynamic calculation.

Rob Collie (00:39:23): Well, I'm going to take the switch X function when it comes back to me. You guys never heard of that, have you? Yeah, that's right.

Bill Sundwall (00:39:29): It's a really obscure function, he's probably never heard of it.

Rob Collie (00:39:31): That's right. You got to turn on the advanced editor index.

Ed Hansberry (00:39:35): It's like a preview section.

Rob Collie (00:39:36): Yeah, it's a preview function. They don't give it to everybody.

Bill Sundwall (00:39:39): Switch X and OPTIMIZEDAX.

Rob Collie (00:39:42): OPTIMIZEDAX, one of the most read blog posts in the history of our... And in fact, ChatGPT uses it. If you give it a DAX statement, and say optimize this. It'll wrap it and optimize DAX, and give it back to you. People who don't know Nara wrote an epic April Fools' post on our website a couple years back about the OPTIMIZEDAX function. You just take your entire measure and wrap it in the OPTIMIZEDAX function. It just takes care of all the performance issues. And people got to the very end of this long article still thinking it was real. There were actually a few people who were a little grumpy about having been taken for a ride, which is how you know you did it right.

Leigh Ann Thibadoux (00:40:20): So one time I saw, it was a YouTube video, it was Power BI Learning, and she had an April Fools', and it was when you're loading data from Power Query into the front end, and it's just sitting there loading, and you're watching it. If you take your computer, turn it upside down and shake it, that it will finish loading all at once. And I totally did it. I totally did it. I was like, "Really? I can do that?"

Rob Collie (00:40:50): We call it an inverted index, right? Isn't that what it's called?

Leigh Ann Thibadoux (00:40:53): And then I saw it on the comments, okay, April Fools' joke. I think I saw it a couple days after April Fools' and I didn't quite get it. I was like, "Gosh, that's really weird. Why would it do that? Okay. Well, I'll try it."

Rob Collie (00:41:05): So Leigh Ann, thank you for sharing that. Look at this. We're all just human beings. Trying that, and then also at the same time, having the conversations that went over my head. But you've already been involved in at least three or four conversations that I didn't understand. It's okay. It's okay to be us. And I really like that. I one time ate an entire hamburger with a toasted bun. While the hamburger patty sat on the plate, he was just sitting there, and I ate the whole thing thinking that I was eating a hamburger. And then I looked around after I was done and said, "Hey, who put this extra patty on my plate?" And it's so thick. And then I argued with people for a little bit that someone had pranked me and put an extra... And it occurred to me that the toasted bun had been just enough to throw me off and I could follow through. When I was sitting there the whole time, I just had never assembled the hamburger. Anyway. All right, Leigh Ann, you're up. It's your pick. This'll be your sixth pick.

Leigh Ann Thibadoux (00:42:06): So I'm going to take one out of your strategy book, picking from a category that has not been picked before. I'm having a hard time deciding, but I think I'm going to pick the DATE.

Rob Collie (00:42:20): DATE. Oh, the one that takes year, month, day. Damn it. That's good.

Leigh Ann Thibadoux (00:42:26): Of all the DATE function-

Rob Collie (00:42:27): That's a good one.

Leigh Ann Thibadoux (00:42:28): ... probably not the best one to pick, but it's one that I've used the most.

Rob Collie (00:42:33): Yeah. So now, I'm going to have to assemble the integer of the date.

Leigh Ann Thibadoux (00:42:38): There was another one I was contemplating though, that I feel like I also would be equally as screwed if I couldn't.

Rob Collie (00:42:45): Hey, what was that? What function was that? No, it's all right. All right. I have my choices. And these luxury picks, we're into the later rounds. I'm taking the BLANK function.

Leigh Ann Thibadoux (00:42:58): Just BLANK?

Rob Collie (00:42:59): Just BLANK, as a return value. To be able to return blank from a measure under certain circumstances. They're like, "You fool." Well, I would just squander this pick. Yeah, that's something I do all the time. There are cases in which a measure doesn't make sense. If you have a change versus prior year type of measure, but then you're at the grand total level of the visual. A better example is change versus prior month, delta versus prior month. And now you're at the quarter level, Q3, what three months should you compare to what three months in a prior month measure at the quarter level of summarization.

(00:43:42): There's no sensible choice there. So I blank it out, and that's my function, and I'm not going to feel any shame about it except I'm feeling shame about it. And I had this idea before you took the DATE function. And again, this shows my primitive nature, but I think the function is FORMAT. The one that lets you do a text format. Without this in calculated columns, most of the time I'll use this in calculated columns is FORMAT. But every now and then I'll use it in a measure. I mean, if I had CONCATENATEX, I would really like to use it. But I don't have CONCATENATEX because dirty, dirty, dirty cheaters. But, yes. I'm taking BLANK and FORMAT.

Bill Sundwall (00:44:16): So FORMAT, I can get behind. BLANK, though, because of the null blank zero semantics of DAX, I almost feel like that's a dead pick. You could always just use zero or you could use open quote, close quote.

Rob Collie (00:44:32): First of all, it's like cartoon physics. I don't understand the... What did you say, the null zero thing semantic. What did you call it? -

Bill Sundwall (00:44:41): The null blank zero semantics.

Rob Collie (00:44:44): I understand what those words mean, but I don't know the concept that the development in DAX that you're referring to with it. You're hinting at something that's going on in DAX that I'm not actually aware of. Because here's the thing. When I wrote DAX, Bill, you are not allowed to return different data types from a branch in a conditional. Is that still true or not?

Bill Sundwall (00:45:03): But even at that, BLANK covers all of your data type possibilities. But what I'm saying is, okay, again, this was a quality of life pick. Because if you have something that's otherwise returning an integer or a decimal, I guess any kind of number, you could use zero instead of BLANK.

Rob Collie (00:45:22): Nope. Not in my charts. Not in my charts, Bill.

Leigh Ann Thibadoux (00:45:25): What if you just don't finish the IF statement? You give the IF statement a condition for true, and then you don't give it a false. What is it? Return-

Bill Sundwall (00:45:35): Yeah, that returns BLANK automatically.

Leigh Ann Thibadoux (00:45:38): But BLANK is good, because you're actually spelling it out, and people can see what you mean, what you meant to say.

Rob Collie (00:45:46): She's saying, "They're there Rob. It's not that dumb."

Bill Sundwall (00:45:52): We're not taking the keys off his keyboard. He could still just put dash, dash, blank, open parenthesis, close parenthesis, and it would accomplish the same thing.

Leigh Ann Thibadoux (00:46:03): So that was actually the excuse I gave in an interview. One time, I was doing a SQL interview with Amazon. I was not great at SQL, very mediocre. And I'm answering the questions and the guy's like, "Well, yeah. But technically, you could have shortened it to this, and it would've been much more efficient." I was like, "Well, I like to spell out exactly what I'm doing in my coding so that people can see what I meant to do." He's like, "Ha, ha, ha. Okay."

Rob Collie (00:46:30): Oh, my God. Here's a true story from my interview at Microsoft. I was asked to write an algorithm or just a CodeBlocks on a whiteboard in whatever language I wanted to use, to test whether just any given one string was a palindrome. Is it the same forwards and backwards? I did it with four loops and I accounted for the difference in cases between it's an even length string and an odd length string. And I did all of that. I did a pretty good job. Then my interviewer who turned out to be a fraud, this was the first time that he showed me he was a fraud, was in the time he was interviewing me. I got done with it. We were talking about whether this code was efficient or not, performance-wise. How long is it going to take to compute? And he told me that there was a way to do this with just two lines of code instead of the multiple lines I'd written.

(00:47:23): And I was like, "What amazing algorithmic trick is he about to drop on me?" And I was excited about this, because this was my nerdery, super innovative, clever, fabric of the universe type shit. And I thought I was going to learn something amazing from him. And he said, "If you include the Standard C library, there's a reverse string function. And then you could have just done a string compare, so you reverse the string, and then do a string compare of the original to that one, and that's only two lines of code." And I sat there looking at him, wondering if he understood what he was actually saying, and he didn't.

(00:47:59): So the thing he didn't understand was that those functions, in order to use them, they import a tremendous amount of code that then gets called and used. It looks like two lines of code, but in terms of number of instructions that the computer has to process, it was at least equivalent to mine. Plus, by including these libraries, he was going to increase his memory footprint of all the shit that needed to be built in and compiled in order to use this. And he had no idea. And he was using this to shame me, to show me that I could grow, and I would grow if I got there. If I had the pleasure of working for him. And guess what? I had to go and work for him for a little while.

Leigh Ann Thibadoux (00:48:36): Wow, this could be much simpler if he just made it into a calculated column.

Rob Collie (00:48:40): Yeah, so much better. I don't know why you would screw around with the measure. So much better for performance. Pre-calculate it.

Ed Hansberry (00:48:46): Yeah, then your DAX, you only need some then you're done. So just make everything a calculated column.

Rob Collie (00:48:52): So that was my seventh pick. Okay, it's back to Leigh Ann. So each of you get two more selections. I only get one more. The last pick in the draft is going to be me, when it comes back to me. Just plan ahead, you only get two more choices.

Ed Hansberry (00:49:04): Actually, Leigh Ann has seven.

Leigh Ann Thibadoux (00:49:06): Did I?

Ed Hansberry (00:49:07): I show Leigh Ann with USERELATIONSHIP, SELECTEDVALUES, SUMX, which I still don't think I have a solution for. SUMMARIZECOLUMNS, CONCATENATEX, which I definitely don't have a solution for, and then the DATE function.

(00:49:19): I thought she took IF?

Rob Collie (00:49:20): She never took IF, we're daring you to try to take IF and IF.EAGER to close this out of conditionals, Ed.

Ed Hansberry (00:49:25): That would be spite, yeah. Okay, cool. Okay, good, good.

Rob Collie (00:49:28): Maybe those two would work as hate picks. Create enough distance between you and the others. One of which being me.

Ed Hansberry (00:49:35): I think SELECTEDMEASURE was enough of a spite pick.

Rob Collie (00:49:39): Oh, yeah. That really hurt me. Hurt me to my core. That function I've never used.

Ed Hansberry (00:49:45): Okay. Sorry, Leigh Ann.

Leigh Ann Thibadoux (00:49:48): Okay. I'm going to stick with the same group of functions that I had last time and pick the NOW function.

Bill Sundwall (00:49:56): Ooh, super handy.

Rob Collie (00:49:58): Mm-hmm. Okay. For the first time, I'm going to dare speak up and say, is NOW really that useful? In my personal experience, the concept of NOW is actually determined by the data and not by the clock. When do you use NOW?

Bill Sundwall (00:50:14): Aging. How old is something?

Rob Collie (00:50:17): Even aging, is aging relative to the last time the data was up to date?

Leigh Ann Thibadoux (00:50:21): Yes.

Ed Hansberry (00:50:21): No.

Bill Sundwall (00:50:23): No, no. How old is this open AR? That's not determined by what's the last invoice stamp.

Rob Collie (00:50:36): I get you. But here's what I'm saying is, for me... I mean, even the last refresh date, the last refresh timestamp is more now than NOW.

Ed Hansberry (00:50:41): Not for accountants.

Rob Collie (00:50:42): Well, problem solved. I'm not an accountant.

Ed Hansberry (00:50:45): You're writing a report for an accountant. They want to know how old something is and receivables or payables versus today, not three days ago when it was refreshed on Friday afternoon.

Rob Collie (00:50:54): All right.

Bill Sundwall (00:50:55): Is by picking NOW, Leigh Ann, also picked to DAY?

Rob Collie (00:50:58): Yeah, she got both.

Bill Sundwall (00:50:59): Yeah, two for the price of one.

Rob Collie (00:51:02): I could screw her over by taking the YEAR function with my last pick.

Leigh Ann Thibadoux (00:51:06): I know. And I thought about with the DATE function, I was like, "Oh, if anybody takes YEAR, MONTH, or DAY. I'm just screwed."

Rob Collie (00:51:14): I'd have to think about which one would be most annoying one for me to pick of those three. I'm too lazy to actually go through that analysis, so I'm not going to do it.

Bill Sundwall (00:51:21): Well, there aren't so many picks left, but you can't exhaust text manipulation. YEAR, MONTH, and DAY are just conveniences. You could always do it with LEFT, if you were in a bad enough way.

Rob Collie (00:51:32): I can't do LEFT of an integer and pull it off, right?

Bill Sundwall (00:51:34): Oh, fair. Yeah, but-

Rob Collie (00:51:36): Because the integer date doesn't actually look like 2022, right? It's days since-

Ed Hansberry (00:51:41): But you have FORMAT, so you could convert it to TEXT, and then do LEFT.

Rob Collie (00:51:46): What is the zero date in all this?

Bill Sundwall (00:51:48): It's either December 30th or December 31st, 1899.

Ed Hansberry (00:51:52): It's December 31st, 1899.

Bill Sundwall (00:51:54): It was supposed to be 1/1/1900, but they messed up.

Rob Collie (00:51:58): They had an off by one error?

Bill Sundwall (00:51:59): I'm pretty certain that's the anecdote.

Ed Hansberry (00:52:01): No, the way that happened... And this is going to go way back. Lotus 1-2-3 screwed up back in the '80s when they created it, and they did not realize that the year 1900 was not a leap year. Because it was evenly divisible by 200 or whatever. And so their model was wrong. Excel kept the error on purpose for Lotus 1-2-3 compatibility, but Tabular did not. And that's why it goes back to December 31st, not January 1st, because Tabular wanted to keep the same integers, so that today... I don't know what today is, but let's say it's 45, 768, whatever. They wanted to keep that the same, and so they just pushed it back to December 31st.

Rob Collie (00:52:46): This is so bizarre. It's like-

Leigh Ann Thibadoux (00:52:48): Such a weird conversation.

Rob Collie (00:52:50): ... this level of detailed trivia is the type of thing I expect from Bill, but maybe not in this domain. In the data-type domain. I wouldn't expect it from Bill. It's like this Bill-Ed hybrid, but it's only Ed. So, Lotus had the off by one and didn't realize it.

Ed Hansberry (00:53:07): I know. Yep.

Rob Collie (00:53:07): Which just explains why they had to change their name to Lotus 1-2-3 from the original Lotus 0-1-2.

Ed Hansberry (00:53:13): Right. That was the original version.

Rob Collie (00:53:15): The dad joke. That's what I bring, apparently.

Bill Sundwall (00:53:17): A zero-based array dad joke. That's like-

Rob Collie (00:53:21): I've got to contribute something to this conversation. What am I supposed to do after Ed holds court on the origins of the date? He's like, "Let me know. Move it all." All right, Ed, you're up for two consecutive picks. These are your last two picks.

Ed Hansberry (00:53:40): All right. So, the first one, since Leigh Ann took DATE, I'm going to take DATEVALUE, so I can at least take TEXT and convert it back to a DATE, and get something usable. So now, I've got all the LEFT, MID, RIGHT functions, and I can work with that. And then I'm going to take TREATAS. Because I think that'll give me a useful workaround for not having USERELATIONSHIP in some cases.

Rob Collie (00:54:03): Okay, attempt to explain. I want this to go over my head again. I've never used TREATAS either, so you haven't deprived me of anything.

Ed Hansberry (00:54:09): So TREATAS allows you to basically say, "I want this table column to filter this other column," and so you're directly saying, "Give me the values in the order date, and I want those to filter the date and the date table, and that's my virtual relationship."

Rob Collie (00:54:28): Okay, okay. Yeah. So this is what I was thinking about doing with the FILTER function, doing virtual relationships, but I wouldn't really know how to do it yet. I'd have to go look for other functions like IN or whatever. There's something in there that allows me to see subsets of shared, and that this spidey-sense-

Bill Sundwall (00:54:47): There's a SQLBI article out there that compares the performance of physical relationships versus TREATAS versus FILTER, INTERSECT.

Rob Collie (00:54:55): INTERSECT. There we go. Yes.

Ed Hansberry (00:54:56): Oh, that's the one you would think of.

Rob Collie (00:54:57): I'll figure it out somehow. I'm still screwed on SUMX though. I don't know what to do about that.

Leigh Ann Thibadoux (00:55:02): I always forget about TREATAS, and then I relearn it, and I'm like, "Wow, that sounds really cool." And then I never use it. Start the whole cycle over forgetting again.

Rob Collie (00:55:09): That's what it is to be human. All right, Leigh Ann, final pick. And by the way, I have no idea what I'm taking. So, take your time. I need to go look at the DAX function reference and see what I've got.

Leigh Ann Thibadoux (00:55:22): Okay, yeah. What I'm going to pick, I'm sure that there are multiple substitutions for this, but I just use the hell out of it. So, ISBLANK.

Ed Hansberry (00:55:31): Mm-hmm.

Rob Collie (00:55:33): That's a good one.

Leigh Ann Thibadoux (00:55:34): I feel like there's probably other ways to work around that.

Ed Hansberry (00:55:39): Yeah, there are, but I use ISBLANK all the time. Because it's easy to read. Easy to read, easy to use. You've got the double equals, and other weird stuff. You've got a full-width.

Bill Sundwall (00:55:52): I'll stall for time for Rob as he figures out his move. What this is really revealing, is that DAX as a language truly only has about a dozen functions in total. The other 340 are just syntax sugar for combinations of those dozen which, thank goodness, because, wow, would this be a heck of a mess if we had to write things in bare metal.

Rob Collie (00:56:17): So am I winning this game? Because now that you all have finished all of your selections, I don't see anything in either of your rosters that allows you to remove filters from something. How are you ever going to get grand totals? Is there a workaround for that? You don't have the ALL function. No one took REMOVEFILTERS.

Bill Sundwall (00:56:34): ALLNOBLANKROW plus UNION of a blank row gives you ALL.

Ed Hansberry (00:56:40): And nobody took REMOVEFILTERS. So if you take it, then that's just spiteful. Because you already have ALL. So-

Rob Collie (00:56:46): Oh, yeah. I could totally-

Ed Hansberry (00:56:47): But to Bill's point, ALLNOBLANKROW would suffice for many, many cases.

Rob Collie (00:56:52): All right. See, I knew I wasn't winning.

Bill Sundwall (00:56:54): Okay, if you really wanted to go for a spite pick, you could grab CALCULATETABLE. Because [inaudible 00:57:00] both FILTER and CALCULATETABLE would significantly-

Rob Collie (00:57:02): No, I'm got going for hate pick. I'm going for... Rob needs it pick. I'm taking TOPN.

Bill Sundwall (00:57:08): Mm. That's nice.

Ed Hansberry (00:57:09): Yeah, I had that on my possible list, so that's a good one.

Bill Sundwall (00:57:14): Especially since it's the last pick. Let's play the game we've been playing. How can you get around TOPN? TOPN returns a table. You could project RANKX onto your table and then filter the resulting table, and still get TOPN.

Leigh Ann Thibadoux (00:57:30): You could SUMMARIZE, add a column with the rank in the added column, and then filter it for whatever number you wanted, if you wanted the top three, and you can filter the added rank column in the virtual table.

Ed Hansberry (00:57:47): Actually there's another function that'll do the same thing. If you use TOPNSKIP with a parameter of zero, it's the same thing.

Rob Collie (00:57:54): There's a TOPNSKIP function. Hey, that's not in the function list on learn.microsoft.com, Ed. I think you're making this up like switch X.

Ed Hansberry (00:58:02): Nope, it's there. It's not on Microsoft Learn. They don't want you using it in the model. It's mostly used behind the scenes and in queries. You can use it, but you have to be very careful, and as long as the skip parameter is zero, then it works fine. But then it's no better than TOPN. But since you took TOPN, Leigh Ann and I can still use TOPNSKIP with zero, and you won nothing there.

Rob Collie (00:58:26): Here's the thing though, I didn't know about TOPNSKIP, so I did win something which is now I can use TOPN. My better nature prevailed, Ed. I wasn't in this to block. If I wanted to screw with you, I would've finished with the SUM function or something. I mean Leigh Ann has SUMX. What are you going to do without SUM?

Bill Sundwall (00:58:45): Yeah.

Rob Collie (00:58:47): TOPN helps me. It helps me because I don't have TOPNSKIP. Doesn't exist. I don't think it exists for you either, for the record, but it definitely doesn't exist for me. This is the problem with having such a limited set of knowledge about something is I can't tell if people are bluffing. I actually believe you. I believe the function exists, but it's not in my list. Do we want to be the people that declare a winner just for the sake of it or do we not? We don't have to be that. We're not going to have zero-sum even though we were completely playing a zero-sum game.

Bill Sundwall (00:59:16): I think the thing we really have to figure out is if the people who took iterators kneecapped everybody else. Because you can definitely do the projection bit through ADDCOLUMNS, but what I would have to sit down and actually type out code to remind myself is if you chuck that ADDCOLUMNS table into a variable, and then you'd probably have to do a SELECTCOLUMNS step to get down to just that column. Can you run SUM over just your projected column and still get to SUMX?

Rob Collie (00:59:50): I can't. Let's be clear. There's two ways to look at this. One of them is these three sets of functions. The list that I took, the list of Leigh Ann, totally, the list that Ed took. In a vacuum, which player do you want to be? If you could choose between these three sets of functions, which one would you choose to be in this game after the game is over, or you can remember who is who, which means I definitely lose. Because all these things you're talking about like workarounds and all that. I have the least ability to use these advanced functions to do those sorts of things. So I'm just hosed. There's no two ways about it.

Bill Sundwall (01:00:24): Who do I want to be perspective, that would probably be Ed. Because calculation groups are significantly powerful, and he has locked himself in on that, and with that pickup of TREATAS, his cases for inactive relationships are not going to run as effectively as Leigh Ann's, between TREATAS, and the fact that CROSSFILTER is in the wild. So he could turn off an active relationship, and then TREATAS some arbitrary table that essentially refires the relationship along the inactive lines. I think Ed is the least kneecapped.

Rob Collie (01:01:01): Right. So I'm going to offer a differing opinion. So Leigh Ann agrees that Ed wins. Okay. I want to commend Leigh Ann's drafting skill. She took this game in new novel directions that we didn't expect. Ed played it the way I would've predicted, incredibly, intelligently, and with all of the knowledge of the space that you know is superior to the competition. I did my thing, which is like give me my set of little rocks that I can bang together. Leading off with USERELATIONSHIP in round one.

Bill Sundwall (01:01:32): That was a shot across the bow.

Rob Collie (01:01:34): The game theory. I'm giving that to Leigh Ann, for sure.

Bill Sundwall (01:01:37): She was all out of bubblegum.

Rob Collie (01:01:40): All out of bubblegum. Well, folks. Thanks for doing this.

Leigh Ann Thibadoux (01:01:43): This was actually a lot of fun and I learned some stuff.

Rob Collie (01:01:47): Thank you, folks. We recorded this on New Year's Eve, eve. So I appreciate you taking time out of your holidays to sit down and do something so ridiculous with me, and doing such a great job of it. This was awesome. Leigh Ann, Ed, Bill. Thank you so much.

Speaker 2 (01:02:02): Thanks for listening to The Raw Data by P3 Adaptive Podcast. Let the experts at P3 Adaptive help your business. Just go to p3adaptive.com. Have a data day.