All About Daxformatter Tool for Power BI: A Detailed Guide 101

When it comes to pulling simple yet insightful data, Power BI has excelled. However, today’s businesses want more – assessing data based on several categories, calculating percentage growth, annual growth compared to peers/the previous year, and so on. This necessitates a higher degree of expertise capable of providing extensive charts and visualizations as well as providing solutions to real-world business questions.

Data Analysis Expressions (DAX) is a collection of Functions, Constants, and Operators that can be utilized as formulas to calculate various values based on accessible data. Power BI Daxformatter has a library of over 200 components that provide you with a lot of freedom when it comes to creating measurements to get the results you want. DAXformatter is an external tool that can be implemented DAX functionalities.

In this article, you will understand the basics of Daxformatter as well as implement the process. 

Table of Contents

• What is Power BI?
  • Key Features of Power BI
• What is Daxformatter for Power BI?
  • DAX Syntax
  • Calculation
  • Functions
  • Formulas
• Importance of using Daxformatter for Power BI
• How to Install and Download Daxformatter for Power BI?
• Understanding the Daxformatter User Interface
• How to Format All Dax Measures at once?
  • Step 1: Install and Download Tableau Editor
  • Step 2: Launch Advanced Scripting
  • Step 3: Execute the DAX Code
  • Step 4: Verify DAX Formatting
• Create Your DAX Formatting Tool in Visual Studio 
• Conclusion

What is Power BI?

Power BI is a Business Intelligence product from Microsoft. It allows users to look at data from many sources and build Reports and Dashboards. It can be used as a stand-alone desktop application or as a fully managed web service hosted in the cloud. While the Power BI Desktop is available for free, the Power BI Service is a subscription-based service that charges users based on how much they use it.

Microsoft has released Power BI Mobile for customers who want to keep an eye on their data while on the go. Power BI may also be used to add analytical tools to custom web apps. It’s compatible with the vast majority of Microsoft’s enterprise software.

Power BI takes advantage of the ability to connect to the most common databases outside of the Microsoft ecosystem and create simple, Interactive Dashboards from them.

Key Features of Power BI

Power BI has a large number of capabilities that set it apart from other BI applications. The following are some of these characteristics:

• You may use a range of Graphical Elements to design your Dashboards. The Dashboards can be printed and shared.
• Dashboards, Data Models, Datasets, Embedded Queries, and many other features are available in Power BI’s “Content Packs.” Instead of searching for the pieces separately, you can use the elements in the “Content Packs.”
• Power BI can connect to most common databases outside of the Microsoft environment and produce easy, interactive dashboards from them.
• It offers a huge selection of visually appealing Visualization Templates. You can create Reports and Dashboards to display your data using as simple or as complex visuals as you choose.
• Power BI has a “Get Data” tool that lets you select from a range of data sources, including On-Premise, Cloud-Based, Unstructured, and Structured data, among others. New data sources are added every month.
• In Power BI, you may filter your datasets to focus on smaller datasets first. This allows you to concentrate on certain data rather than the full dataset all at once.

To get further information on Power BI, you can check out the official website here.

What is Daxformatter for Power BI?

Microsoft launched Data Analysis Expressions (DAX) as a mathematical language for defining computations and queries in Power BI, Power Pivot, and Analysis Services Tabular.

SQLBI’s DAXformatter is a free application that converts your raw DAX calculations into tidy, readable code.

1) DAX Syntax

Any DAX query and expression can be formatted in the following way:

• EVALUATE …
• Table[column] = …
• Table[measure] := …
• Table = …
• [measure] := …
• [measure] = …
• measure := …
• measure = …
• = …

2) Calculations

Measures, calculated columns, calculated tables, and row-level security all employ DAX formulas.

A) Calculated Columns

A Calculated Column is one that you add to an existing table (in the model designer) and then specify the column’s values with a DAX formula. When a computed column contains a valid DAX formula, values for each row are calculated immediately after the formula is input. The in-memory data model is then used to store the values. When a formula is entered into the formula bar of a Date table, for example:

= [Calendar Year] & " Q" & [Calendar Quarter]

B) Measures

Measures are Dynamic Calculation algorithms whose output varies based on the situation. Measures are used in reporting when numerous attributes are utilized to combine and filter model data, such as in a Power BI report or an Excel PivotTable or PivotChart. The DAX formula bar in the model designer is used to create measures.

Standard aggregation functions, such as COUNT or SUM, can be automatically constructed by using the Autosum feature, or you can define your own formula using the DAX formula bar. Measures with names can be used as arguments in other measures.

A Tooltip feature displays a preview of what the results might be for the total in the present context when you define a formula for a measure in the formula bar, but the results are not immediately output anywhere else. Since the outcome of a measure cannot be determined without context, you won’t be able to see the (filtered) results of the computation right away.

A reporting client application that can provide the context needed to retrieve the data relevant to each cell and then evaluate the expression for each cell is required to evaluate a measure. A PivotTable or PivotChart in Excel, a Power BI report, or a table expression in a DAX query in SQL Server Management Studio(SSMS) might all be clients.

For each cell in the results, a separate query is conducted, regardless of the client. That instance, in a PivotTable, each combination of row and column headings, or each selection of slicers and filters in a Power BI report, provides a new subset of data over which the measure is calculated. For instance, consider the following simple measurement formula:

Total Sales = SUM([Sales Amount])

C) Row-level Security

A DAX formula must evaluate a Boolean TRUE/FALSE condition with Row-level Security, defining which rows can be returned by the results of a query by members of a specific role. The Customers table, for example, has the following DAX formula for members of the Sales role:

= Customers[Country] = "USA"

Members of the Sales role can only see data for customers in the United States, and aggregates like SUM are only returned for customers in the United States. Excel’s Power Pivot does not support Row-level Security.

When you use a DAX formula to define Row-level Security, you’re constructing a permitted row set. Other rows are not denied access; they are simply not returned as part of the approved row set. Other roles can grant access to the rows that the DAX formula excludes. A user can examine data for a row if they are a member of another role and that role’s Row-level Security permits them access to that row set.

Security formulas at the row-level apply to the selected rows as well as associated rows. When a table has many relationships, the active relationship’s security is applied via filters. Security formulas defined at the row-level will be intersected with formulas defined for linked tables.

D) Calculated Tables

A Calculated Table is a computed object derived from all or part of other tables in the same model and based on a formula expression. A DAX formula defines the table’s values instead of querying and loading values from a data source into the columns of your new table.

In a role-playing game, Calculated Tables can be useful. The Date table, for example, can be ordered as OrderDate, ShipDate, or DueDate, depending on the foreign key relationship. By specifically constructing a calculated table for ShipDate, you obtain a standalone table that is queryable and completely operable like any other table. Calculated tables can also be used to create a filtered rowset, as well as a subset or superset of columns from other tables. This allows you to maintain the original table while developing modifications to support unique scenarios.

Relationships between tables are supported through calculated tables. Data types, formatting, and data categories can all be found in the columns of your calculated table. Calculated tables, like any other table, can be named, surfaced, and concealed. If any of the tables it pulls data from is refreshed or modified, computed tables are recalculated.

3) Functions

Within an expression, a function is a named formula. As input to most functions, there are mandatory and optional arguments, often known as parameters. When the function is called, it returns a value. DAX offers functions for doing calculations with dates and times, creating conditional values, working with strings, performing relationship lookups, and iterating over a table to execute recursive computations. Many of these functions will appear fairly similar if you are familiar with Excel formulae; nevertheless, DAX formulas differ in the following crucial ways:

• A whole column or table is always referenced by a Daxformatter function. You can use filters in the calculation to use only specific values from a Database or Column.
• Many of the functions in DAX return a table instead of a value. The table is used to supply input to other operations rather than being displayed in a reporting client. For example, you can fetch a table and count the unique values within it, or calculate dynamic sums across filtered tables or columns.
• A number of time intelligence capabilities are included in the DAX functions. These functions allow you to provide or pick date ranges and execute dynamic computations based on them. For example, you can compare sums from different time periods.

A) Date and Time Functions

DAX Date and Time functions are identical to Microsoft Excel’s date and time capabilities. Daxformatter functions, on the other hand, use a datetime data type that dates back to March 1, 1900. See Date and time functions for more information.

B) Aggregation Functions

Aggregation functions compute a (scalar) value for all rows in a column or table as determined by the phrase, such as count, total, average, minimum, or maximum. See Aggregation functions for further information.

C) Financial Functions

Financial functions are used in DAX formulas to do financial calculations like net present value and rate of return. These features are similar to those found in Microsoft Excel’s financial capabilities. See Financial Functions for more information.

D) Relationship Functions

In DAX, you can utilize connection functions to get values from another related table, select a specific relationship to use in an expression, and set cross-filtering direction. See Relationship functions for further information.

E) Logical Functions

When a logical function is applied to an expression, it returns information about the expression’s values. The TRUE function, for example, tells you whether an expression you’re evaluating produces a TRUE value. See Logical Functions for further information.

4) Formulas

DAX formulas are required for establishing computations in calculated Columns and Measures, as well as Row-level Data Security. Use the formula bar along the top of the model designer window or the DAX Editor to create formulas for calculated Columns and Measurements. Use the Role Manager or Manage roles dialogue box to construct formulas for Row-level Security. The information in this part is intended to help you learn the fundamentals of DAX formulas.

Basics of Formulas

DAX formulas can range from simple to sophisticated. The table below illustrates various simple formulas that can be used in a computed column.

You can use the following stages to build a formula, regardless of how basic or complex it is:

• The equal symbol (=) must appear at the start of each formula.
• You can type or pick a function name, or you can type or select an expression.
• Start typing the first few letters of the function or name you want, and AutoComplete will show you a list of functions, tables, and columns that are accessible. To add an item from the AutoComplete list to the formula, press TAB.
• You can also use the Fx button to see a list of functions that are accessible. To add a function to a formula, use the arrow keys to highlight the item in the dropdown list, then click OK to add it.
• Choose the arguments for the function from a dropdown list of suitable Tables and Columns, or type them in manually.
• Check for problems in the syntax: make sure all parentheses are closed and that all columns, tables, and values are appropriately addressed.
• To accept the formula, press ENTER.

Importance of using Daxformatter for Power BI

• Being a Power BI user and learning DAXformatter is similar to being a Power BI user and learning how to apply formulas in Excel. You were able to arrange your tables, add some charts, and click the sum/average/… button (Σ), but then you were introduced to the realm of VLOOKUP, IF functions, and other such functions. However, this comparison isn’t entirely accurate because Power BI is already a tremendously powerful tool even without DAXformatter, whereas anything more than light use of Excel necessitates the use of formulae.
• Learning DAX, on the other hand, will open up a whole new world of Power BI for you. The ability to dynamically pick, connect, filter, etc. data is the most important function you will uncover. This means that the Dashboard may take user input and use it to build computed Columns, Metrics, and Tables dynamically.

How to Install and Download Daxformatter for Power BI?

It is not necessary to download and install DAXformatter in order to utilize it. You can use the DAXformatter by going to the official website and entering the URL. Alternatively, you can download it as part of the PowerBi.tips package to have it as part of your External Tools on the External Tools ribbon in Power BI Desktop. You can Download the Business Ops form here. You can download versions based on your browser settings when you do this through the Business Ops option, as shown in the diagram below. 

Understanding the Daxformatter User Interface

The DAXformatter UI, in contrast to the other External Tools, is rather simple and straightforward. Let’s discuss each numbered section in DAXformatter briefly using the diagram below.

• Point 1 is the first pane that displays the DAX code that needs to be formatted.
• Point 2 is where you paste the prepared DAX code into Power BI Desktop.
• Point 3 is where you can copy in HTML format (but I’ve never done so!).
• Point 4 can be used to store the DAX code in a word document.
• Within the DAXformatter UI, point 5 is utilized to re-edit the DAX code.
• Point 6 closes the current code and opens a new blank pane in which to paste fresh code into Point 1.

How to Format All Dax Measures at once? 

Step 1: Install and Download Tableau Editor

On your computer, download and install the most recent version of Tabular Editor. After that, the external tool tab will appear on your PowerBI Desktop. Now, Open Tabular Editor and navigate to your file.

Step 2: Launch Advanced Scripting

Go to Advanced Scripting, next to Expression Editor, and select Tabular Editor to format each line. To see it in action, copy and paste the code below.

foreach (var m in Model.AllMeasures) 
{ 
   m.Expression = FormatDax(m.Expression); 
}

Step 3: Execute the DAX Code

Click on the green button or hit F5 to run the code. Wait a few minutes till you notice Script Executed Successfully in the Tabular editor’s bottom right corner.

That’s all there is to it, and you’re ready to go. Remember to save your work.

Step 4: Verify DAX Formatting

Close the Tabular Editor and go to the Power BI desktop to double-check your results. All of your measurements have been formatted, which is fantastic. You’ve completed the task!

As a result, you’ve simplified the formatting of all DAX Measures. However, it’s worth noting that this approach only formats Measures, not computed Columns.

Create Your DAX Formatting Tool in Visual Studio 

This section demonstrates how to use Visual Studio Code to construct your own DAX Formatting tool from scratch. The tool may then be added to the External Tools button on the Power BI Ribbon, allowing you to beautify all of your DAX expressions with a single click.

HASHCODE

• Since this is a free endpoint, it must be ensured that any helper tool doesn’t overburden the API, so the script here is written in a way that minimizes the number of times the API is called.
• The goal is to save a HASHCODE value in an Annotation that represents the DAX Expression. In Analysis Services, an annotation is a place where you can store text that isn’t directly engaged in the calculation but may be valuable for other reasons, such as documentation.
• If a DAX expression does not have an annotation, such as a measure, calculated column, or calculated table, we submit the text to the API and get back some formatted DAX. The prepared text is converted to a HASH value and stored in the annotation.
• The HASH value from the annotation is created and compared to the HASH value over the current expression the next time the script runs. The DAX expression is still formatted if the two values match, therefore there’s no need to send it to the API again.

SCRIPT

You must follow the steps below to get this up and running.

• Download and install the .Net Code SDK (version 3.1 or version 5.0)
• Download and install the most recent version of Visual Studio Code.
• Open Visual Studio Code and create a new project folder.
• Run the following command in the terminal window to create project files.

dotnet new console 

• Install the essential Packages:
  • AMO Client Libraries 
  • Newtonsoft.Json
• ALL code from the Asset Folder’s Program.cs file should be copied into your copy of the program.
• Use Power BI Desktop to run the script on a PBIX file.
  • Check to see if the model has been upgraded to V3.
  • Line 16 should be updated with the port number for testing (not required if launched as External Tool)
• Transfer the External Tool JSON file from the Asset Folder to your machine’s External Tools folder.
  • To point to where your app is created, open, update, and adjust the path attribute.

Conclusion

This article teaches about Daxformatter. It provides in-depth knowledge about the concepts behind every step to help you understand and implement them efficiently. You can integrate many such platforms using Hevo.

Visit our Website to Explore Hevo

Hevo Data, a No-code Data Pipeline can seamlessly transfer data from a vast sea of 100+ sources to a Data Warehouse, BI Tool like Power BI, or a Destination of your choice. It is a reliable, completely automated, and secure service that doesn’t require you to write any code!  

If you are using Power BI as your Data Analytics & Business Intelligence platform and searching for a no-fuss alternative to Manual Data Integration, then Hevo can effortlessly automate this for you. Hevo, with its strong integration with 100+ sources and BI tools (Including 40+ Free Sources), allows you to not only export & load data but also transform & enrich your data & make it analysis-ready in a jiffy.

Want to take Hevo for a ride? Sign Up for a 14-day free trial and simplify your Data Integration process. Do check out the pricing details to understand which plan fulfills all your business needs.

Share your experience of learning about Daxformatter! Let us know in the comments section below!