DAX Formulas Clean Up Tool: Some Updates To Logic

For today’s post, I want to do a quick overview of our DAX cleanup tool and how you can use it to format your formulas. This epic tool will solve a lot of formatting issues inside of your Power BI models. You may watch the full video of this tutorial at the bottom of this blog.

If you’re relatively new to Power BI, formatting your formula properly is absolutely crucial. If your formulas are not formatted, they will be complicated to understand and will hold you back from learning DAX well.

For example, if you browse the Enterprise DNA forum, you’ll see people just starting out with Power BI that are putting formulas into threads that aren’t formatted. Unfortunately, it is so hard to understand what’s going on in these reports. That’s why we’ve invested in building this app, and why we’re still investing.

We’ve made a lot of updates to the logic embedded within the app since its release. I’m also giving you a sneak peek at what we’re going to add to the app very, very soon.

Download Free Resources Here

Table of Contents

The DAX Cleanup Tool: How Does It Work?

All you have to do is copy your formulas in the app, and it will automatically format it for you, no matter what type of formula structure you put in initially.

This app is part of our Analyst Hub platform, which we’re building in stages at the moment. There’s a range of different apps here, with the DAX cleanup tool being one of them.

Some of these formulas have already been cleaned up by the cleanup tool using the old logic that we had.

In recent weeks, we have updated the logic embedded in the app to make it more condensed and presentable. I don’t like formulas that are spread out long. I wanted to have customized logic to make the formula more condensed and easier to read.

This is an issue I’ve had with some of the tools out there, which is why I decided we’re going to build our own exactly the way I want it.

I’m going to use some formulas from the forum. When you read this particular formula, it’s too hard, right? If you are a relatively new Power BI user, this looks way more complex than it probably should.

Select the formula in your Power BI file, copy, then paste it in the cleanup tool. Finally, click on the Format button.

And just like that, it transforms into a perfectly formatted formula.

Simply copy the code, go into your model, and then place it into a measure.

How To Edit Your DAX Formulas

But what if there’s something that you don’t like in the formula? For example, maybe you want to put a space in between the variables. You can click on the Edit button to make your changes.

Once you’re done making the changes, click on the Commit button, and then click on the Copy Code button. You can make the adjustments in the formula within the tool itself.

How To Format Your DAX Formulas

The app also shows different ways to format. Let’s say for instance, you don’t like the formula written this way. You can change it into something with shorter lines.

But personally, I prefer the longer lines.

That’s why we’ve developed this tool: to give you more flexibility.

You also have the option of putting in space after function or no space after the function. The Best practice option will show you how I would have done it.

If you have numbers in your formulas or if you want to use semicolons, you can use the Separators option. I don’t like to use semicolons, so I usually go with just the comma.

If you want to do another formula, just click on New. I’m going to copy another formula from the forum.

I’m going to paste it in the cleanup tool, click on Format, and see what comes out.

This one is so much easier to read, right? All that’s left to do is to copy this into our model.

Let’s choose another formula from the forum and copy it into the cleanup tool.

This formula will turn into this format when using the tool:

With this tool, we don’t want to say that this is the way you should do it; but rather, we want to give you options. We can give you some automated options, but we also want you to be able to edit the formula however you like.

Other Features Coming Out Soon

Very soon, you’ll be able to save your formulas. So if there are formulas that you use over and over again in your models, you can create a catalogue of these within the app – you no longer have to type out lengthy formulas. You can just reference and copy the saved formulas to your models.

We’re also going to add a community feature where we can collect an entire index of all the formulas that everyone has been creating within the Enterprise DNA community forum, and embed it into the app. This means you’ll be able to reference what others have done as well.

Conclusion

I’m really happy with the way the DAX cleanup tool has been formatting things. This is how I would format codes if I was writing things out manually. I love what we’ve created here, and excited about where it’s going in the future.

This app will speed up your development like you wouldn’t believe. If you’re excited about this as I am, don’t forget to subscribe to Enterprise DNA TV for more tips and techniques.

Talk to you soon,

Sam

Download Free Resources Here

***** Related Course Modules *****
DAX Formula Patterns
Ultimate Beginners Guide to DAX
Demo Data For Practice