Thinking Outside the Box – Ep. 242

Performance tuning doesn’t always mean rewriting measures. Sometimes the fastest win is changing how you ask the engine for results.

In Episode 242, Mike, Tommy, and Seth talk through a real-world report that was crawling (15+ seconds) and the counterintuitive fix: switch tactics in the visual layer—using constant lines from the Analytics pane—so the engine does less work while the report still tells the same story.

News & Announcements

• Devil is in the detail! Become a Power BI champion by thinking out of the box! — A great reminder that performance isn’t only a DAX problem: a small visual redesign (constant lines) can slash Formula Engine work while keeping the business logic intact.

• Submit a topic idea — Send questions and real-world scenarios you want the crew to debate.

• Subscribe to the Explicit Measures podcast — Get new episodes and browse the archive.

• Tips+ Theme Generator — Build consistent, reusable Power BI themes.

• Mike Carlo on LinkedIn — Episode updates and Power BI/Fabric tips.

• Seth Bauer on LinkedIn — Engineering perspective on analytics and delivery.

• Tommy Puglia on LinkedIn — Leadership and real-world BI lessons.

Main Discussion

This episode is basically a permission slip to stop reflexively ‘optimizing DAX’ every time a page is slow. The team walks through a concrete workflow: capture the query your visual generates, test it, and then consider whether the visual strategy is forcing the model into extra work.

A key insight from the linked write-up is that if your chart lines represent slicer-driven thresholds (i.e., constants for the current selection), you don’t necessarily need the visual to compute and render them as full series. By shifting those lines to the Analytics pane as constant lines, you can reduce the complexity of what the visual requests—often cutting Formula Engine time without changing the measure logic at all.

Key takeaways:

• Start with measurement, not guesswork: use Performance Analyzer to capture the visual query and validate it in DAX Studio.
• Pay attention to where the time goes (Storage Engine vs. Formula Engine) so you optimize the right layer.
• Visual design choices can change query plans: the same measures can trigger extra intermediate tables and joins depending on the visual type.
• If you’re drawing thresholds/targets, consider constant lines (Analytics pane) instead of calculating full line series inside the visual.
• Sometimes the best optimization is removing work (extra tables / NATURALLEFTOUTERJOIN-style shaping), not rewriting business logic.
• Treat performance like an experiment loop: change one thing, retest, compare, and keep the change that produces a repeatable win.
• ‘Thinking outside the box’ in Power BI often means reframing the problem: ask what the report needs to communicate, then pick the least expensive way to render it.

Looking Forward

Pick one slow page this week, capture the visual query, and experiment with a small visual redesign before you touch any measures.

Episode Transcript

0:08 thank you [Music] good morning everyone welcome back to the explicit measures podcast with Tommy Seth and Mike good morning everyone how’s it going good morning good morning

0:39 just jumping back in where this is a pre-recorded episode we have some people training so just we are traveling so that being said we are just making it clear real quick here about this episode as being automatically already being pre-recorded however we definitely encourage you still to chat with other people in the chat during the live video either on YouTube or LinkedIn because those people are real we’re just not here right now now we’re in spirit always again right now so that counts right for those who are on audio on audio doesn’t matter nothing changes for you

1:11 doesn’t matter nothing changes for you continue that’s true that’s right yes that’s right we’ve never been real to you we’ve always been an afterthought not really just a voice in The Ether exactly exactly so on the on the podcast previously we talked a little bit around Tommy you had you have a writing a a nice bike one day and you’re going across oh it was a gas station and someone clipped you on the back tire it was it I clipped they hit the frame and snapped they hit the frame and they snap stuff yeah so I love

1:41 frame and they snap stuff yeah so I love not thankfully Tommy’s the money maker the face part was all okay with no issues but their bike was very damaged and is that a commission you haven’t been biking for a while I’ve been borrowing a buddy’s bike but he’s much taller than me so there’s a lot of like leaning and going okay and then I was I like the it’s an older bike and I went tried to go earlier this week again I got a flat tire and if you were in the Wheaton Chicago area you would have just heard a loud whelp got really frustrated so

2:13 loud whelp got really frustrated so drove with my wife to get it bikes now in the lake it is the bottom there’s cinder blocks tied to it it is gone it was basically we been talking about it for a while but I said my wife said I think I got a pretty good employee of the year award all right all right so we got a new bike and it’s got it is nice man it’s it’s a long time coming my wife I think is happier for me but she knows what it does for me mentally just so right Seth I know you

2:45 mentally just so right Seth I know you mentally just so right Seth I know this it’s got mechanical yeah oh that’s nice yeah yes I was writing it because I got like my bike computer Breeze again data analysts everything goes with the cloud oh yeah totally I think I’m pushing one of something to fabric from Strava but there we go three kpis average speed heart rate and miles and it I’m going like 22 miles an hour and I’m like I got the knees that’s awesome so it is nice very nice that’s impressive actually

3:17 very nice that’s impressive actually that you lasted this long because Mike I don’t know how I don’t know if you’ve ever but if I ever exercise no I don’t I do not no no no no no long distance like with with the nicer bikes like when you really get into it and I’m not nearly as into it as Tommy is but like they they you can you can go get fit on a bike right and there’s there’s like a little little this way a little that way a little whatever because oh yes your hands can go numb yes doesn’t feel right yeah so like Tommy riding somebody else’s bike

3:49 like Tommy riding somebody else’s bike for this I was like wow that’s great impressive that’s that’s dedication Tommy that’s dedication to get out there it’s a Cervelo it is okay that’s cool well and Tommy so the reason I bring this all up is because Tommy sends us a picture he’s he’s done his bike ride he’s got his bike in the air two hands up he looks like a rocky pitcher like I can see it right now and I was on my phone and I got the picture and I so badly wanted to replace the bike with

4:19 badly wanted to replace the bike with the fabric just like just champion of the world with a fabric logo on top of it like I might like Fabric in her background my Twitter Banner if you do that that’s pretty good that would be amazing anyways that’s what I thought of when I when I saw your picture but congratulations Tommy happy you got your bike back happier back onto your routine it’s weird when things mess up your routine yeah and and my wife will tell me I think you need to go on a bike ride I’m like I’m fine you need to go on a bike ride yeah just get

4:50 go on a bike ride yeah just get the energy out awesome awesome well very good well that moving on to our actual I think there’s really no connection between the intro topic and the actual topic today so I topic and the actual topic today so the devil’s in the detail it’s the mean the devil’s in the detail it’s the details that I was just talking about maybe bring your bike to your size your frame like there’s a lot of tweaking I like it these these Specialists and bikes know exactly true what what to tweak and where to where to make you as comfortable as possible

5:20 make you as comfortable as possible there’s your intro there we go it it reminds me of the analogy of the gentleman who goes to fix an an engine inside a big boat right and they charge you an absorbent amount of money and he comes out a little tiny hammer and pink hits the engine and it starts running it’s not it’s not because it’s not because I it’s not because the hammer hit cost ten thousand dollars was because I knew where to hit that was the point the same same idea here tuning and tweaking things this is in that way we could we could extrapolate that same analogy to an article from

5:52 that same analogy to an article from data Mozart Nicola has written a really good article here is and articles called devil is in the detail looking at becoming a power bi Champion by thinking outside of the box there are times when you will find you’ll build a report page where the report page is slow for whatever reason you’re pulling data in but understanding how the visual side of power bi Works in relation to the the analysis Services engine side is something you learn kind engine side is something you learn over time I don’t think day one you

6:23 of over time I don’t think day one you really understand all the connected pieces between the front end and the and the analysis the cube side but this article goes into depth here around talking about there’s a clustered column chart by dates and starts talking about adding some error bars upper and lower bounds and so by doing that a certain way it forces the engine to do extra work so I guess high level is have you you let’s let’s maybe go at a high level or

6:55 let’s let’s maybe go at a high level or high concept here right you build a report report the report has a visual on it that’s what the the customer is asking for and it’s just hog slow it just takes forever to render have you guys encountered that before before I I’ve done it twice not yeah just twice just twice in my attire okay in nine years I’ve only done this twice nine years yes all my 15 years of power bi so right yeah but man there’s been a

7:25 yeah but man there’s been a and it’s always been because some convoluted Dax query majority of the time in terms of like like okay well we got to do this ear cumulative rolling 12 month period and it works and I can see it and then once we do it in the visual that they want because it’s always with the category that they want all often everything just slows down you’re like okay it works totally with this but not with this this with not with this and it’s just trying to figure things out because of course always it’s going to slow for what they

7:55 always it’s going to slow for what they actually want it to be grouped with correct or there’s we want to include all these calculations except these other things or something becomes iterative across the table and by doing that that’s what’s causing the challenge or the problem with the data model so look maybe before we talk directly about the article how do you guys go about debugging that what is your pattern for when you find like how do you identify on a page where’s the slow visual like usually my gut tells me certain things but like

8:25 my gut tells me certain things but like how do you how is there anything you use data wise is there any parts of the tool that you use I have some ideas let’s I’ll hear what you say let’s jump over to Seth maybe move Seth where do you go digging for slow stuff first it’s been a very long time since I’ve gotten so so when you remember doing your report building I I think in in general it usually try to lock down whether it’s the visual a combination of the calculations within the visual or just the calculation itself and so I’ll rip apart part particular visual or use performance analyzer to like see what’s

8:56 analyzer to like see what’s going on as far as page rendering yeah to to at least get down to like hey it’s this thing that’s taking a while and then start to decompose it much in the same way that I would do with a SQL

9:05 same way that I would do with a SQL query back in the day a lot yeah deconstruct the query to figure out like is there a particular area of slowness that I can just address right off the bat before I dive really into the weeds and start looking at the query plan and and whether or not I’m missing indexes or et cetera Etc so just in general like those are those are the starting points yeah we’re getting into the weeds so I think I take a very similar approach you you can Auto detect

9:35 you you can Auto detect what’s on the page which is slow because when you click something or refresh a property or parameter you can see the little yeah the little Spinning Wheel right initially so and you can typically you’ll see like a lot of visuals just populate quickly those are the ones that Finish First and automatically render there’s usually that one or two visuals maybe maybe there’s more the visual just sits there and Spins I just there’s a little tiny wheel and it’s hard to see because sometimes it’s interesting too and I’ve seen people fall

10:05 and I’ve seen people fall through this a bit I don’t know if you’ve has also seen this as well but sometimes I’ll see people go into a report and they’re like the data’s not changing I’m like actually it’s still rendering you didn’t notice that the visual was there the line chart or whatever the bar chart of the table was still there nothing changed it wasn’t like as instantaneous as you’re used to however if you look in the upper left hand corner you can see it’s still spinning and then and that’s to me that’s like a visual indicator to me like oh it’s of these four or five visuals on a

10:36 these four or five visuals on a page it’s usually this one visual I feel like I feel I feel like I find this the most in tables I feel like that’s the one visual that I find this the most in you say that trends for you as well Tommy I was gonna say I think you and I have talked about this before and we have a very similar approach I would rather have the loading visual with nothing there because you’re like okay I can can blow this up but it’s always like you add another field to that table and then everything starts slowing down you’re like oh it’s true no I I think you and I do something

11:07 true no I I think you and I do something similar I’ll create a new page with that visual yep I’ll copy that visual over to another page and we like okay I think it’s this visual let’s just isolate this one visual so there’s maybe something because you could have a weird filter on the report page that’s causing something odd to go on do I is it do I have unnecessary like like a hierarchy so I’ll try the table I’ll try some groupings to see if it speeds it up because I want to know first if it’s the model I always check first is it something with my model so if I try a higher level grouping with three categories let’s speed it up sure and

11:39 categories let’s speed it up sure and then from there it’s diving into the Dax like okay what do I let me let me message Greg there should be I’m gonna make a new external tools add-in that’ll just be Greg’s email address or Greg’s slack Channel hey quickly slack Greg baldini ask him about this question I’m sure I’m sure he would be fine with that if like every time it was clicked it was like a hundred dollars yes exactly so I like that idea Tommy and I I think

12:09 so I like that idea Tommy and I I think I I one point I want to point out here around this is there there is there are some other tools that I like to use after I get done with like looking at the visual side and so one thing that in my head that I continue reminding people and I think there’s a feature that I use a lot more in desktop now especially when I’m teaching people about the reports that we’ve built them so what we’ll do is we’ll report we’ll then do like a quick little hey here’s a quick overview of what your report does yeah a lot of times I like putting visuals on the page

12:39 times I like putting visuals on the page I don’t like a lot of tables just because if you want a table that usually means you’re exporting things we’re not highlighting the data that you care about we’re giving you the summary or the aggregated form of information but when you need to get to the detail there’s usually a table that being said said when you look at the report page every visual has a table behind it and if you use the Ellipsis on the table you can see that okay this visual was whatever it is a column chart whatever it is when

13:09 it is a column chart whatever it is when you click view as table it shows you the visual and then shows you all the data behind it which is really nice so so for every visual you make there is a logical table or there’s a table of information that is being produced that then tells the the desktop this is how you’re going to render this table and from there I’ll typically hop into performance analyzer and that’s the performance analyzer inside desktop you have a little separate pane for it and then when you refresh yeah and then what

13:39 then when you refresh yeah and then what you can do is you can which is really cool is it lets you get the evaluate tax statement for that visual out of desktop and at that point in time I usually leave desktop because I’m look I’m interested in the table to your point Seth like I’m trying to figure okay which measure in this table causes the slowness what is what is doing what is happening here and so at that point then I jump over to DAC studio and not many people understand what Dax studio is for or how to use it aptly and that’s where I spend a lot more of my time I’m going

14:09 I spend a lot more of my time I’m going through that query and I’m basically rewriting or slowly deleting out things from that query to figure out okay what part of this query can I is is causing the slowness where does the slowness come from and then I’m literally just dropping columns until it goes fast and then say okay it’s probably one of these three and so I’ll do a lot of testing there there to start tweaking the measures themselves and identifying which measure is the root cause of the problem I think it’s underrated and just cannot be understated how powerful the

14:40 be understated how powerful the performance analyzer and grabbing the Dax query from a visual can be yes I agree with that because it just for those who have are are uninitiated to that you can do an entire performance analyzer on a page but that basically looks at the querying between visuals the interaction but if I want to say hey what is the query because every visual is a Dax query whether you like it or not you can grab that query that basically saying the group by this the filtering everything that’s making up how the numbers come to be pull that

15:10 how the numbers come to be pull that into Dax Studio which is a easy install using power bi business Ops an external tool tool and it just will show you per in a sense per execution per I guess query the sub queries how long each is taken you go wow this little filter here that I had no idea was occurring is taking up eighty percent of the entire load time here here yeah yeah so I think okay what’s interesting you mean about this article is like it’s not a it doesn’t track me as like oh very

15:41 a it doesn’t track me as like oh very deep thought-provoking one that we’ve talked about different articles before but at the same time I think it’s being blogged because Nicola must have been excited about the solution right where he outlines like hey there’s this cluster bar chart he he walks us through how he gets to this point where he uses the analyze feature to simplify essentially this Dax query output yep which speeds up the visualization rendering exponentially right because with the filter panes and selections of

16:13 with the filter panes and selections of measures or values that are being selected in the filter pane those are what’s driving the addition of these lines on the chart so all in all I think it’s a good nugget for somebody to read through put it in your brain dictionary of like hey if I ever encountered the situation I can use this what strikes me more is where we’ve gone with the conversation a little bit though is is it speaks to the breadth of experience I think that he has right so if you’re thinking if you

16:44 has right so if you’re thinking if you read through the article what are the core components that he’s utilizing to come up with this right he understands stack Studio he understands front-end back end like the whole thing is like you everybody’s default is typically oh dive into the Dax right yes these are these are very well known things for him he can read Dax well right because it’s you’re now getting not what you’re creating and putting in you’re seeing the optimized layer underneath the covers of what’s actually being

17:15 covers of what’s actually being generated yes and then he’s also including other features right and pointing out that hey even even Within the community of power bi users there are a lot that don’t even know what this magnifying glass button does because you don’t interact with these visualizations so I think yes that’s what’s most interesting about like if when we start talking about thinking outside the box precursor to that is understanding all

17:46 precursor to that is understanding all of these features right like the we we preach that a lot it’s not you’re if you’re using this tool and you see this a lot too with with bi developer power bi developers yeah totally know what they know they won’t you only know what you’ve you’ve you’ve actually gone and built before if you’ve never used bookmarks if you’ve never used filter if you’ve never used these things you don’t even know how they act on the page right and it’s by testing and doing

18:13 right and it’s by testing and doing these things that broaden your horizons that give you a much wider array of options to choose when you think outside the box because if you don’t create a bigger box where there’s a lot of other stuff in there for options of you solving problems you’re going to have a harder time and that’s where I think people struggle with thinking outside of the box because they don’t have a big box like there’s only so many ideas

18:45 yeah and I like the idea so another thing here that’s going on as well so it’s a very deep understanding the front-end back-end and another I really hear that I think would be relevant for people to research here is he talks a lot about where do you analyze where the queries come from and this is concept of the formula engine and the storage engine and so this is a very core concept of what analysis Services is doing do I need to go back to the disk essentially right yeah and go grab data or can I just grab things already aggregated in memory or already

19:17 already aggregated in memory or already aggregated in form so I could run faster so I think that’s probably oversimplifying the storage engine and formula engine I think a bit of hot cold yes yes right it’s hot it’s ready to serve up yep and then in the back it’s not right like exactly gotta do some work to get a good answer yeah so if you if you read down to the bottom part of the article right anytime you can do visual building again a general rule of thumb that I think pertains here is yes this is a very specific example this was using

19:48 specific example this was using something with a visual that’s creating error bars right but in this case he used measures that were generating these bars across the page and then by changing the formula slightly it was enabling an ignore function so he wrapped an ignore function around some information which basically caused the engine to not make two tables instead just make one large table right so again it’s that deeper understanding of how the engine works and being able to try different things to get the same results

20:19 different things to get the same results and this is one of the reasons why I think it’s very difficult for people in Dax Dax you can write the same equations three or four times and get the same number a correct number but then now you can start talking about are you tuning those measures are you designing those things to be fast and efficient for what’s occurring inside the report and I think that’s that takes things to another level where you and initially You’re Building sums and averages and but then at some point in time you have to understand what filter context and evaluation context looks like because that changes how you build

20:50 like because that changes how you build things potentially and that’s when you are really that’s when my head hurts typically right I’ll build a couple normal measures we’ll get the model kind normal measures we’ll get the model together okay great we’re all happy of together okay great we’re all happy then we start adding context Transitions and filter contexts and that’s when you’re like oh my goodness what is it doing when I write this formula mm-hmm so I I to me that points to a couple things simplify your data model as much as you can get rid of stuff that you don’t need if your Dax statements get very complex

21:20 statements get very complex potentially There’s issues there as well well but in this scenario using or understanding this this article and again I’m gonna have to dig into a bit more of this ignore function and do a bit deeper dive on what he was actually writing in these DAC statements to understand how he was able to manipulate the store the engines I don’t think he did the drop I think that’s that’s the automatic happens automatically yeah so yeah

21:52 add a line of code to everything it was by manipulating the visual right oh yeah that’s what changed that the deck accidentally changed the Dax right from from joining two tables to basically making the measures variables I think and then ignoring the data that it didn’t need to gotcha like rip across so I I think to me bringing it out like not into the specific realm of like his his challenge but what are it’s easy I

22:23 his challenge but what are it’s easy I think for us to to say oh yeah think outside the box but what is that what does that mean for you guys like what are some of ways that when you’re in when you encounter something like he did something’s taken 13 and a half seconds and it’s not going to be a direct Dax input that’s solving the problem because I guarantee that’s where he started oh sure yeah I’ll probably try to like see if this is optimized and it is okay it’s the filter in injecting in I’m gonna go in a different direction what are what are some ways you guys troubleshoot

22:54 are some ways you guys troubleshoot issues related to like thinking outside the box because the it’s not it doesn’t fall into the oh I’ve seen this before Realm Realm that’s a good question I think that there’s probably three areas there that we could have a three-hour conversations there’s power query issues loading ETL there’s probably the visual trying to get a representation for the client and then there’s probably just data quality the way I immediately go which each have their own approach

23:25 each have their own approach or methodology whether it’s something I’ve done before or trying something new but it’s hard to say oh do you would approach them the same way right for specific problems yeah I would agree they’re all you’re all gonna approach them in different ways because they’re going to be presenting different things to you yeah I guess where I was at was thinking on a it how do I let me let me answer let me answer real quick Seth maybe that’ll

23:56 answer real quick Seth maybe that’ll help spur your thought because I think you’re you’re refining the question and maybe my answer might help or find that question a little bit so when I so at some point this happens with everyone at some point whatever you’re working on you run out of knowledge you’re just out yes right so so the question here is I have something that seems to be slow or I have something that’s causing me some angst or problem around whatever we’re building and we’re you you basically have come to the end of I’ve already tried three different things or two different ways of doing the same

24:26 two different ways of doing the same problem something else is missing here so one step I think I’d try to take is I try and go back down to the most simplest basic form of what we’re doing so I try and simplify things for one so if something’s getting overly slow or complex I think is there anything in here that I could remove to simplify that’s probably one of my one of my thoughts that I’m going through the second one is I have a couple friends and and or employees in my company who are also really good at Dax and I think having another person looking at your

24:57 having another person looking at your problem and be able to articulate so part of the problem solving process is to be able to explain what is the problem so in some cases I’ve actually tried to explain my problem to somebody else and in me explaining the problem they were like okay let me just let me just listen to what you’re going to say I explain it to them and in the explanation I’m like oh that’s that’s that’s where it is I see it now like so sometimes just explaining it to someone

25:28 sometimes just explaining it to someone else illuminates where I was missing something the weakness the the calculation that I was missing and then I can solve it that way so that happens also and then probably the final way is like I still don’t know what I’m doing I get it to somebody else and someone else comes with fresh eyes and they’re like well why are you doing this way and they just ask me a couple simple questions around well why not do it like this or why not replace that value with something else oh okay I can see that and so you with

25:58 can see that and so you with somebody else beat up the idea together together so again this is where I I’m not sure it’s quite like a mentor but it’s the idea is do you have access to someone else who’s doing similar things who’s at the same level as you at some point it’s very helpful to ask questions of them and maybe they’ve encountered something similar maybe they’ve dealt with a similar challenge but then they can help you pick apart the idea from a different angle so other people will have different backgrounds and experiences using that as a reference

26:28 experiences using that as a reference point point can also help you understand the engine better by using their knowledge too those are my three ways I think I would probably try to tackle something that I just I’m at my Wit’s End and again of course I’m Googling things believe it or not now I’m using more of Bing chat and Bing chat is actually doing a pretty dang good job good job of of if I ask it a question it’s doing a really good job of telling me what the answer is and then showing me where the

26:58 answer is and then showing me where the answer reference came from another reference point that I’ll put there as well is I’ll go to the power bi Community or if I Google something typically I land on an article from the power bi blog or the community around the blog and they’ll help me out as well too I I gotcha so I don’t know if this is the name of how the song goes but one

27:17 the name of how the song goes but one huge ways break down break it down I don’t know that’s maybe a 90 song so I will always break it down whether even if it’s a simple Dax query I’ll have a few pages where if I know it’s some something lagging or something maybe not supposed to be a different number you’re dealing millions of rows trying to get like even like a manual account okay if this is this then this is this then it should be one okay and

27:48 is this then it should be one okay and I’ll create some custom function or custom measures that are literally just doing a one or zero for me to say should it be true and then if this is true then this should be one and really breaking down there if it’s a more complex Dax query that’s slowing things down I will create Dax functions for or Dax measures for each of those sub queries to try to break it down one to see you is something this like the expected result and then two how slow is that son of a gun but

28:18 how slow is that son of a gun but breaking it down and I’ll do I’ll do one Mike suggested early on in this podcast is take a subset of the data a few tables from there and just try to break it down that’s true because there’s some things I’m dealing with where it’s like okay there’s a million rows and each of these have a million sub rows or like 20 sub rows per per column good luck figuring that out what the numbers should be that’s true so a lot of times I if you’re trying to iterate quickly through some

28:48 trying to iterate quickly through some ideas it’s much harder when the model is very large and there’s also data quality problems too yeah so if you have data quality problems and the form is running really funny it adds too much like too many variables in my mind and so I always like to your point Tommy I like going down sometimes I’ll build out a sample example okay does this sample example run very slowly can I do to optimize that one particular piece that will work as well you answered the question like exactly where I was going

29:18 question like exactly where I was going right and and in the way I think I my main point would have been by simplifying the problem right it’s a great teaching tool like and I think some of the best experience I’ve had is is actually presenting because it teaches you how to take a concept and simplify it for presentation to speak it’s true people a lot of people through the idea so it’s the same learned thing you’re repetitively taking

29:49 learned thing you’re repetitively taking Concepts or things you learn and articulating them and I think in these really challenging and I’m not talking about simple problems like you’re going to build your your your dictionary of problem solving skills as you go along but regardless you’re always going to run into something that stymies you like you’re stuck and a lot of these are very complex to even like you’re you’re so far in the weeds or you just you’ve tried so many different things that it’s

30:19 tried so many different things that it’s hard to simplify them but but to Tommy’s techniques breakdown like using subsets of data approaching it differently right pieces of it ones and zeros making assumptions about the data like this is what it should be this is this is the output I would expect because as as people who deal with data all the time that it it at least once you find a specific problem area area it alludes to the larger issue right if

30:49 it alludes to the larger issue right if you solve that one like you could have multiple problems but at least you’re going to solve one of those and yes this is especially true in data quality right yes yes something’s not right here what is the use case the one thing that is off oh my goodness okay that that relates to this business logic that’s that’s wrong right so yes it’s a learning process but what I’ve also found though is in in the past where you’re really hitting your head against a a challenge

31:19 hitting your head against a a challenge right and and maybe this is one of those cases for Nicola because it’s like oh the Dax is optimized what do I do this 13 and a half seconds this makes no sense sense to get to the point where you can articulate that sometimes I’d just go take a walk right you need to remove yourself that’s a good point take a break from from the problem that you are trying to solve and separate yourself a bit because because it gives I think a lot of the techniques in here are such that you you’re trying

31:51 in here are such that you you’re trying to trick your brain out of the pattern that you’re in because the pattern that you’re in is not the one that’s going is not the one solving the problem so articulating things simplifying the problem forces you to rethink about how you would explain what the problem is to somebody and break it down into these components but if you can’t get there yet take a break let your brain think through like it is always going to be chewing on this problem until you solve it trust me right so that’s Tommy on a

32:21 it trust me right so that’s Tommy on a bike that’s I think wow that’s somebody holes in their carpet but also I think what that does is it breaks it out into components and I really like that thinking backwards also does that right like if you start at the front of the problem and like try to think of different ways to attack like what it is you’re building then you you break yourself out of the mold where you’re at and then ultimately you’ll come to I think the the solution on your own

32:51 own or to mic to your other point which I really like is that’s where you start to leverage other opinions right like throw it at somebody else like if you can’t like if you’ve simplified it if you shared it with somebody and they can’t figure it out they don’t know what the problem is then yeah open it up into to other areas areas well and that’s a good point when you have 50 tabs open on your browser with 10 of them being Google searches just a different way maybe it’s time to close a few tabs and just take a

33:22 close a few tabs and just take a take a break there is some really gold nuggets and I the community even if I find something that’s not exactly what my problem is but like Google prop you my problem is but like Google prop shows it up I will always always know shows it up I will always always download someone’s example that they did it’s like that’s what I’m looking for but not really in I don’t think that’s going to work I’ll still download that example and look at the Dax code so what are you doing and can I have maybe I can apply something

33:53 can I have maybe I can apply something and I’ll just test things out again this is where that sample file or a new version of the file with smaller data comes in handy yes because then it’s not like oh everything’s slower now because I have 10 more measures that I’ll say Dev tests yeah true and and to some point too I don’t even want to mess up my real main model with bunch of testing functions or if I’m tweaking Dax I don’t really want to mess with that I want to just have it works in the side to see what it’s doing yeah I would be lying if I’ve accidentally overwrote the main measure wait what yeah oh no that’s the

34:24 measure wait what yeah oh no that’s the worst another one I just thought of that I’ve used sometimes if you’ve ever do you guys ever you create mind maps like in Vizio or Lucid or like whatever the visual recently whatever visual representation is I find that yeah fascinatingly successful for myself right we’re interesting breaking out breaking out complex anything into a conceptual pieces yeah where I can look at it as I think through the problem a lot of the times I figure out the breakdowns or I figure out what’s what’s

34:55 breakdowns or I figure out what’s what’s missing within that and it helps me articulate in my own head like what am I seeing why why am I why am I seeing this why why what would make that happen why is that happening and then the whys start coming coming in and it helps narrow down a potential Gap in logic a potential Gap in like whatever’s going on or maybe it’s something I need to go like it’s leading me towards oh nothing about that studio well I’m okay well if that’s where I’m going to find whether

35:25 that’s where I’m going to find whether or not I have Dax problems but maybe I should go learn that right true very true I use those as well I I don’t know if I’ve ever used a mind map to go through a challenge like that and actually that makes that makes a lot of sense though I’d like I’m gonna have to try to incorporate that into my workflows here when I get stumped on something but what does the Mind map of things look like well walk me through like if you’re is this for like debugging a Dax statement it says from something specific like how are you mind mapping these things are you just writing ideas down on a

35:56 you just writing ideas down on a page and like arranging things together like say in general but you can do it with any problem though right yeah if if if if it’s the same hierarchy that we were just talking about I forget what what episode right like where you’re you’re making an assumption I would assume that the like what is what is happening here here are all the things I’ve tried already what are the results it’s almost like a a science experiment type thing oh I’m trying this

36:20 experiment type thing oh I’m trying this yes it did it okay no no no whatever right like yes but here’s why here’s why what other options do I have because with with challenging problems I’ve also found in the past that I’ll start redoing the same thing and I think it’s I think it’s different just because it’s a different part of the data data set because you can find yourself in rabbit holes and what this allows me to do is make sure I’m not retracing the same Trails especially so it’s true like some of these like really complex data

36:50 of these like really complex data problems I just don’t understand why they’re happening and so I it’s hard for me to articulate like like oh it’s this same process every time because it’s not because every problem is different to Tommy’s point before like power query visualization data quality governance like a workflow whatever the case may be yeah it it just helps me visualize not just like where I’m going but where I need to go right here’s where I’ve

37:21 I need to go right here’s where I’ve been here are the next steps I’m going to take to to help myself triage this and and I have my my whole area of like okay I’m still stuck I’ve done 20 things let me take a step back look at what I’ve what what I’ve done already those looking at like the like the these pieces help me figure out like what the next step is because you can find yourself in these deep holes where you’re just absolutely flustered and stuck because

37:52 absolutely flustered and stuck because you don’t know what the next step is yes or you don’t remember what you’ve tested last or it’s been a couple days we know that super visuals so that’s that’s where that’s where I you I start using that because it’s like whether it’s whether it’s that or not it’s almost this is the this is why I drive my folks to before you’re even working on things right like what is the bullet list what what is the the the scope of problem that you’re trying to solve because when we’re in this ambiguous realm of oh I’ve got a I’ve got a bunch

38:23 realm of oh I’ve got a I’ve got a bunch of things to figure out there’s a lot of nuance in the data and what does that mean right yeah what is it clearly write that down and yeah and that’s not just like I think for the the precursor to that question is like I need you to be able to articulate like the the scope of the problem for me so I can plan accordingly I was gonna say I was gonna on your point there yeah that’s the exact point it’s much more more helpful for you to communicate

38:53 communicate to me and thereby help yourself in figuring out what are what is the specific areas I need to address here instead of it being this nebulous monster of like oh there’s so much stuff you bring up an interesting point here as well around what happens when there are multiple problems at the same time and I think you’re you’re mind mapping here as well you could have three or four issues all at the same time and I think this is what Tommy was going to be referring to a little bit earlier which was around well maybe part of the reason why your visual or behavior is slow is

39:24 why your visual or behavior is slow is because there’s actually something wrong in the data there’s a data quality problem there’s there’s a loop that’s occurring or there’s a value that’s causing problems to not make it render correctly so you you could be facing a Dax problem you could be facing a visual problem you could be facing a data quality problem all at the same time and then by mind mapping out I’ve tried this and this and this isolating certain things you can then more closely drill into what is officially the root cause of the problem and then you can spend time rebuilding or rethinking things

39:55 time rebuilding or rethinking things on how it would calculate something I I will try that mind maps have never been my my Forte or it may it’s harder for me to visualize or do a mind map than the actual thought process itself if that makes sense I know how easy it is to press the plus icon yeah I’m not saying that yeah I see what you’re saying yeah I I’d I I’d I I really do I’m I should try that though because usually the majority of time it’s not one Dax like line right it

40:27 time it’s not one Dax like line right it could be multiple factors in that is how many visuals or it is could be the model and breaking that down I would love to see a template or example that I’ve used in the past for something technical with the Mind map well obviously we’re an audio podcast podcast well I don’t think initially when I was thinking mind map I was thinking like a like a mental map where there’s like a branching tree type thing I don’t think that’s what you’re referring to yourself I think you’re literally just referring to almost the point of sit down

40:58 almost the point of sit down write the problem at the top of the page problem problem and then as best you can articulating out what is the problem you’re dealing with and then okay test one here’s what I’m doing test two here’s what I’m doing test three here’s what I’m doing okay and then basically taking those areas of the board and again right now a program that I’m really loving is Miro which makes this very easy you can quickly type and text things all over the screen and it gives you yeah yeah it’s it doesn’t have to be like a very organized thing it could just be literally Landing of information and I

41:28 literally Landing of information and I what I do agree also here Seth is too sometimes you need a little a couple data points to help you reference what you did right so okay you may make this change the DAC statement okay paste that DAC statement here in Miro okay great the statement’s there here’s the performance numbers that I got and we just did we did a very similar thing with tuning some data bricks clusters for one of our clients same similar concept right we had a long-running job we’re like okay it’s going to take us X number of hours to process all this data historically how can we cut that data

41:59 historically how can we cut that data down and what we did is we did a at your same point we made a mental map it was in Excel we just kind mental map it was in Excel we just wrote down the different parameters of wrote down the different parameters we were changing and we kept changing one or two parameters at a time we tried to keep it just a one as much as possible that way we could actually performance tune oh okay if we had a larger cluster size if we had this bigger machine if I added more memory it went faster okay adding seven machines or eight machines or 16 machines didn’t really go as fast as adding five machines that were just bigger right so

42:29 machines that were just bigger right so you can do the comparison back and forth fourth and we were able to tune a process that took eight hours down to like three hours right so great we we had a 60 increase roughly on performance that’s the stuff you’re looking for yeah I use Excel when I know where I’m going more right like in a direction sure I’m gonna test out these things here’s the metrics I’m going to collect I use I actually use mirror and Lucid or not mirror Lucid right just yeah yeah but it’s very similar I just work in

42:59 but it’s very similar I just work in bubbles well for some reason because I can move them around into conceptual areas right and I think where I’m at is like okay I I run through my basic my basic things in troubleshooting here’s what I know but I’m when I’m stymied that’s where I’m like okay I tried this thing it fits into this bucket this is where I will try whatever the results of it were were et cetera Etc and then I find that I’m rearranging some of this stuff as I’m working my way through a problem and

43:29 working my way through a problem and that’s where I just love the visual too I love having a canvas in these bubbles of thoughts or tests or ideas that I can rearrange as my thinking about the problem changes because a lot of times you’re you’re pushing into a new Direction where you’re like oh okay maybe it’s over here right okay what are the things I need to go look up or look at or whatever the case may be yeah and then I have my list of next steps to take and then what’s really nice about that is and this is what I’m saying like

44:01 that is and this is what I’m saying like with challenging problems is when somebody comes in they’re like when when you’re at the point where I’m like I can’t figure it out Mike I need you to come take a look at something with me yeah what did I what did I told you how how can like one of the and the reason why I love this isn’t so so effective is how do you bring somebody into a complex problem without forcing them to go through just a huge ramp of knowledge that like or troubleshooting that you’ve

44:31 that like or troubleshooting that you’ve gone through right because what they’re going to do is they’re gonna say I say hey Mike I’ve got this really complex problem I don’t know how to figure it out what have you done Seth I I’ve run through all of my battery of tests What specifically well and then I can list them all off but there’s inevitably going to be a bunch that you’re going to try again that I’ve already tested yes that I’ve already done so we’re wasting time because you’re doing the same repeated things that I did because you didn’t know what I did but if I’m keeping track of that

45:02 keeping track of that then we can I can bring you up to speed a lot faster yeah and or eliminate the same repetitive tests that you would go through and we can narrow down into the problem area much faster I like that idea because it again it’s a it’s about a knowledge transfer at some at some point point right you’re trying to knowledge transfer into the next person and say I’m trying to get you up to speed quickly right so we’re not wasting time

45:25 quickly right so we’re not wasting time anymore it’s not gonna it’s not every time I use this right because but it is like it’s the squirrely ones it’s the ones that you’re like I don’t know how to solve this okay when you hit that point is when I usually start saying look okay I’m gonna start cataloging what I’ve done because I’m already in the weeds pretty heavily and if I need to like pull somebody in here with me I want to be able to articulate where I’m at with them that’s a great point you at with them that’s a great point or you’re gonna find yourself there know or you’re gonna find yourself there anyway when we say you have to simplify the problem and and be able to

45:55 the problem and and be able to articulate somebody you have to go through these same steps anyway you might as well start them to help yourself out before yes yeah totally totally I I do have one that is relatively new for 2023 that I’ve really been using these like for me sometimes I don’t want to put my my problems on Twitter or my pride gets in the way of asking somebody of like I don’t know this but I probably should is definitely writing it down I do

46:26 is definitely writing it down I do have a folder of all like Dax queries that I know I’ve used before that are like really important like rolling average cumulative thing like I and I I keep it in vs code in a private GitHub repo because like I know I’m gonna need to use this yes at some point and just take a look but it’s actually created a Dax prompt that’s called Dax assist and now that GPT can upload like Excel files and read it oh I basically say hey I’m gonna tell you the model and I’m going to reference some tables here’s a some demo data and I’m

46:59 tables here’s a some demo data and I’m going to tell you basically what I’m trying to do with this and it’s the same idea I’m not just saying break down this code would I say like here’s the three five rows yeah I’m using AI but it’s because they already have this for like Python scripts and then I remember Marco Russo had a YouTube an hour thing of him just using chat GPT earlier this year and saying what he liked what he didn’t like and he’s like I wouldn’t use this but it works in the most very Italian way yeah they’re like it might not be the best but if it works

47:30 might not be the best but if it works for chat gpp Associates thing but but he did he did say that he was surprised with the results he could be slow but it would get him generally in the right direction so I have this problem saying analyze this I’m going to ask you about the data model here’s my desired result interesting I’ve had I’ve had a customer actually reach out sometime and said hey I got this Dax measure it I got I asked chat to EPT and it gave me the answer which naturally led to the when’s Microsoft coming out with co-pilot for for power bi what is that when does that show up

48:00 bi what is that when does that show up and I’m like well not sure how long it’s gonna take I haven’t seen any really good demos yet so other than what we’ve seen it build so far so I think it’s a bit of a ways out at this point it’s gonna be great is you guys as consultants are going to face if you haven’t already somebody across from you asking you questions they’ve already asked chatgpt and then oh yeah at some point well Chachi PT doesn’t agree with you Mike yeah yeah justify your answer Jimmy she’s wrong obviously they’re obviously their data

48:30 obviously they’re obviously their data so this is also probably why the reason why the more things that I talk about the more I need to put them on the website yeah then chat GPT can go reference myself it’ll be great when someone asks chat gbt a question and like oh I found an answer right power bi tips oh okay great right so it was right that was mine that was me you’re chatty PT oh my God if the shoe fits that’s awesome well but now that you

49:00 that’s awesome well but now that you found out your rate is going up right exactly exactly exactly right I I am a data source to chat GPT chat GPT so with that I think we’ve really exhausted this one this has been a really good topic I think this was very good very good if we had a wrap here wrap this topic around put a bow on this one this one what would be our our secret sauce we’ve been doing this for nine years what would we say for someone who’s trying to dissolve complex problems or look at visuals that are

49:30 problems or look at visuals that are particularly slow if we had to give our final wrap up here of what we record what we would recommend what would you give maybe your one one or two words of advice Tommy let’s go with you let’s start with yours first I’m gonna go back to the hits Top 100 Billboard breakdown I think just breaking it down simplifying whatever you’re looking at on your page which is that one visual that one query and then just slowly breaking down the components of that to see does this get

50:00 components of that to see does this get what I want does this get what I want this one and then slowly work your way back reverse engineer it cool Seth any what would be your kind Seth any what would be your one nugget here that you of one nugget here that you would take away from this saying that you would yeah I recommend I think it’s in two parts right we the more time you invest in learning the larger your box and the easier it is you’re going to be able to think outside of that right like think about different solutions solutions and then simplify and or slash force yourself to

50:30 yourself to change the pattern of thinking to read like re reevaluate the different components and drive towards like different things you can check and test them them I like your I like your idea there around around learning more increases the size of your knowledge the size of the box that you can figure out problems with gives you more options right yeah totally and this didn’t work what else can I try yes correct right I’m not going to stop at like in a perfect example is Nicola

51:01 like in a perfect example is Nicola you’re not going to stop at this visual I’m not going to just error bars other ways that I can create this visual well I know or I’m gonna go look at here or I’m gonna it makes it very clear at the bottom of the article he never changed the Dax the Dax did not change it was literally just I was using error bars and now I’m using constant lines just the difference between those two what is another way I can solve the problem same problem yeah what that does is like I like to learn and grow like you have more of those things that come to mind or as options to test

51:32 to mind or as options to test that really points in my mind the idea that you have to know that there is an error bars you have to know there is a Content line you have to know that you can drive a constant line based on a formula a measure that’s driven that same thing so really interesting I really like that that line of thinking but also does really help you it really emphasizes you really should know the tool like you should go through all the options you should click on all the buttons you should try more things in the tool to round up that box a bit more if you have

52:03 round up that box a bit more if you have 15 years of experience of power bi you really should know the tool you should hopefully unless you have 25 years what do we have seven 75 years now 85 years of I don’t know things came out in 2015 right so it’s something like that eight eight years right eight nine years somewhere around there that’s all you’ve been walking around around so I guess I’ll give my last word of advice I would also recommend put some people around you that know what they’re doing or find people that are at your skill if

52:33 or find people that are at your skill if not higher and trying to connect with them right because you will run into problems challenges issues and it’s nice to have a second person to bounce an idea off of and I also think the effort of trying to explain the problem problem explain why it’s broken also helps out there as well so I think those will be maybe my word of advice as well find some other people in the community around you where you can ask the question right Tommy we wouldn’t necessarily ask all of our questions on Twitter because that’s kind

53:03 questions on Twitter because that’s kind questions on Twitter because that’s showing we don’t know what we’re of showing we don’t know what we’re talking about but having that that that’s that closed loop of people that there’s no dumb questions hey I must be missing something here simple can you help me out with this I’m doing this thing that’s that’s very helpful and I ask Seth all the time why I can’t I can’t do things because you I can’t I can’t do things because I just can’t and it’s he know I just can’t and it’s he he does he just tells me how many to get and moves on and and we figure it out and move on forward I think there’s a fair share of that that goes back and forth

53:34 find closed conversations but we don’t have to we don’t have to air that one out on Twitter there so thank you all very much for watching and hanging out with us for another podcast we really appreciate your listenership we appreciate your ears if you found this informative if you found some insights here that would help you build your own things in power bi our only ask is if you would wouldn’t mind share with somebody else let somebody else know you found some value from the podcast with that Tommy where else can you find the podcast you can find it on Apple Spotify or wherever you get your podcast make sure

54:05 wherever you get your podcast make sure to subscribe leave a rating if it’s available in a comment it helps us out a ton do you have a question a topic or just something you want us to talk about on the podcast on a future episode you can go to powerbi. tips slash the podcast leave us a mailbag idea and finally join us live every Tuesday and Thursday 7 30 a. m and all power bi. tips social media channels thank you all very much and we’ll see you next time

Thank You

Thanks for listening to the Explicit Measures Podcast. If you enjoyed this episode, share it with a coworker and drop a question or topic suggestion for a future show.