Enter your email to track your progress and stay up-to-date on new Power BI learning content.
By clicking Track progress, you are giving your consent to Microsoft to provide you updates about Power BI learning resources via email. You can unsubscribe at any time.
As you progress through Guided Learning, a green checkmark will appear next to your completed topics.
With an introduction complete, get ready to dive deep!
There are two primary calculations you can create using DAX:
Before digging into creating either of those, it's good to have a firm grasp on DAX syntax for tables and columns, which you will use when creating either calculated columns or calculated measures.
Whether you're creating a new column or measure, it's important to know the general format of table names in DAX:
If there are spaces in the table name (as shown above), the single quotes around the table name are mandatory. If the table name has no spaces, the single quotes can be omitted, so the syntax looks like the following:
The following image shows a DAX formula being created in Power BI:
You can also omit the table name completely and just use the column name, but this is poor practice for writing clear functions (and thus, for clear DAX code). Column names must always include the square brackets.
It's best practice to always do the following:
Calculated columns are useful when you want to slice or filter on the value, or if you want a calculation for every row in your table.
You can create calculated columns in Power BI Desktop by selecting New Column from the Modeling tab. It's best to be in Data view (rather than Report or Relationships view), since you can see the new column created and the Formula Bar is populated and ready for your DAX formula.
Once you select the New Column button, the Formula Bar is populated with a basic column name (which you change to suit your formula, of course) and the = operator, and the new column appears in the data grid, as shown in the following image.
The required elements for a calculated column are the following:
If you reference a table or column in your calculated column formula, you do not need to specify a row in the table - Power BI calculates the column for the current row for each calculation.
Use a calculated measure when you are calculating percentages or ratios, or you need complex aggregations. To create a measure using a DAX formula, select the New Measure button from the Modeling tab. Again, it's best to be in the Data view of Power BI Desktop since it shows the Formula Bar and makes it easy to write your DAX formula.
With measures, you see a new measure icon appear in the Fields pane with the name of the measure. The Formula Bar is again populated with the name of your DAX formula (this time, with your measure).
The required elements for a calculated measure are the same as they are for a calculated column:
Video content courtesy of Alberto Ferrari, SQLBI