Looking for a good material on DAX for beginners? You are not alone. As I started learning DAX some 2 years ago, very often I got frustrated (I still get) with intricate, difficult to understand explanation that I found on google. They confused rather than enlightened me. Explaining DAX in a simple way was what I needed at that time (and I still need now).
And here it is, a new book „Beginning DAX with Power BI: The SQL Pro’s Guide to Better Business Intelligence“ by Philip Seamark. The most user-friendly
material on DAX so far, in my opinion. I really enjoyed the KISS (keep it simple stupid) step-by-step approach of the first chapters, in particular the three DAX Calculation samples.
Here are some excerpts from the first chapter of Philip Seamarks book. I hope you will enjoy reading it as much as I did:
„…It’s possible to import data and have no need to write in DAX. If data is clean and simple and report requirements are basic, you can create a model that needs no user-created calculations. Numeric fields dragged to the report canvas will produce a number.(…) The default aggregation over your numeric field will be SUM. This can be changed to another aggregation type using the properties of your visual. Other aggregation types include AVERAGE, COUNT, MAX, MIN and so on. In this approach, the report creates a DAX-calculated measure on your behalf. These are known as implicit measures . Dragging the ‘Fact Sale’[ Quantity] field to the canvas automatically generates the following DAX statement for you: CALCULATE( SUM(‚ Fact Sale'[ Quantity])) This calculation recomputes every time a slicer or filter is changed and should show values relevant for any filter settings in your report.
Most real-world scenarios require at least some basic enhancements to raw data, and this is where adding DAX calculations can improve your model. When you specifically create calculated measures, these are known as explicit measures . (…) These are the three types of calculations in DAX:
Calculated columns
Calculated measures
Calculated tables
Your First Calculation
(…) When viewing ‚Fact Sale‘ table in Data View, we see the unsummarized value for each row in the [Total Including Tax] column. A calculated measure is required to show a single value that represents a sum of every row in this column. In Power BI, you can create a calculated measure using the ribbon, or by right-clicking the table name in the Report View or Data View. This presents an area below the ribbon where you can type the DAX code for your calculation. The text for this calculated measure should be:
Sum of Total including Tax = SUM(‚ Fact Sales'[ Total Including Tax])
The structure of the formula can be broken down as follows: starting from the left, the first part of the text sets the name of the calculated measure. In this case, the name is determined by all text to the left of the = operator. The name of this calculated measure is [Sum of Total including Tax]. Names of calculated measures should be unique across the model including column names . This name is how you will see the measure appear in the field list as well as how it may show in some visuals and charts.
The = sign separates the calculation name from the calculation itself. A calculated measure can only return a single value and never a list or table of values. In more advanced scenarios, steps involving groups of values can be used, but the result must be a single value.
All text after the = sign is the DAX code for the calculated measure. This calculation uses the SUM function and a single parameter, which is a reference to a column. The single number value that is returned by the SUM function represents values from every row from the [Total Including Tax] column added together. The datatype for the column passed to the SUM function needs to be numeric and cannot be either the Text or DateTime datatypes.
The notation for the column reference is fully qualified, meaning it contains both the name of the table and name of the column. The table name is encapsulated inside single quotations (‘ ’). This is optional when your table name doesn’t contain spaces. The column name is encapsulated inside square brackets ([ ]).
Calculated measures belong to a single table but you can move them to a new home table using the Home Table option on the Modeling tab. Calculated measures produce the same result regardless of which home table they reside on. Note When making references to other calculated measures in calculations, never prefix them with the table name. However, you should always include the name of a table when referencing a column.
Philip Seamark (2018-03-31). Beginning DAX with Power BI: The SQL Pro’s Guide to Better Business Intelligence (Kindle Locations 522-567). Apress. Kindle Edition.
If you would like to learn DAX join one of our Power BI courses in Vienna