Are you looking to get an introduction to DAX and learn more about this powerful tool? In a recent webinar, hosted by Nick Lee, you will be introduced to the basics using the DAX language to create calculated columns and calculated measures. In addition to the basics, this session will introduce you to creating aggregates, conditional functions, and time intelligence calculations.
The Data Analysis Expression language (DAX) was specifically designed by Microsoft to be easy to learn and powerful, especially if you’re comfortable with Excel and Excel formulas. It’s simpler than traditional technical languages to learn and is designed to support a larger user base. DAX is used for analytical functions on top of your data set you already have; with just the basics of DAX you can significantly improve the analytical value of your data model.
DAX is used for calculated columns, which are descriptive fields that you’re adding on to your model for tables you already have and is a method for connecting disparate data sources with multiple key columns to consolidate your data fields for users. It’s also used for calculated measures which focus more on your numeric/metric data, as opposed to calculated columns which is more for text type data.
This webinar covers DAX basics such as Table, Column and Measure Reference, as well as conditional and logical functions, which you can use to build out some IF, THEN, ISBLANK type logic. The demos in this webinar cover creating calculated columns and measures, as well as these conditional and logical functions.
Questions and Answers from Webinar–Training on the T’s–Introduction to DAX 4/17/18
Can the search/find function reference a predefined list or named category that includes several key words?
Not inherently, no. What you may be able to put together is a formula that utilizes the LOOKUPVALUE function to find exactly what you need.
What if you wanted to sum profit/sales/costs/etc. for a particular product or business unit?
In a visual, like a table for instance, you can just drop in the fields that you want after you create the measures, and apply the measures as well. Once you do so, the measure will filter to whatever the context is on the table and you'll be able to get your total profit/sales/costs/etc. for every product listed.
How did you hide columns?
In Power Query Editor, you can Right Click on the column(s) you want to hide and select "Hide Column".
When calculating someone's exact age, what about leap years?
The formula we used for calculating someone's exact age will still provide the same result, regardless of the birth date.
Can we get this presentation after class?
Yes, all of the webinars we create are visible for free on pragmaticworks.com. Direct link to all of the Training Courses we've done so far are found here: https://pragmaticworks.com/Training/Courses
I think an easier to understand dax formula for calculating the age would be: ROUNDDOWN(DATEDIFF(Table1[Birthday],Table1[Today],MONTH)/12,0).
The example you gave is a good example and could be something we add to our DAX Cheatsheet or something we even slide into the Advanced DAX class as an alternative method of doing something while introducing functions we may not have discussed. That being said,
- Conceptually I think the one we use is a little easier to understand and work through.
- The one we use creates a reusable pattern that can be used for other things, not just the birth date.
- The primary reason we used the other expression is because it allows us to show off multiple things in DAX. For example the longer example we use shows how to use conditional logic (IF statement), the DATEDIFF function, and the FORMAT function.
Is this training part of Power BI training package?
Yes, you can find out more here: https://pragmaticworks.com/Training/On-Demand-Training/Power-BI-Pack
Are all text functions one based and not zero based? In other words, the first character is 1 and not 0?
Yes, Power BI's first character is always "one based", so the first character is always referenced as the number 1 rather than 0.
Is it necessary to include line breaks in the formulas?
Nope! Line breaks are not necessary at all. But, when you get to creating more complex DAX calculations, especially ones that may need to be updated at some point, it is much easier to read and comprehend what the formula is exactly doing with line breaks included.
Can we get access to the slide deck?
Sure, here is a link where you can download the slide deck. https://file.ac/OOOsG3BFZmU/DAX%20Intro%20Slides2.pdf
Please explain the difference between count and countx again thanks!
COUNT is specific to a single column, and it counts the number of rows that contain either numbers, dates, or strings. If the row contains text that cannot be traslated to a number, then the row is not counted. The COUNTX function allows us to extend the operation of COUNT, and take an expression (as an arugment) that is evaluated over a table. By doing so, COUNTX enables us to perform independent calculations and subsequently take a count of the rows that translate to a number/date.
Does it support regex?
DAX does not have any dedicated functions for RegEx. To use RegEx, you will need to be using R scripting.will there be any sample data provided for testing?All of the scenarios we went over on this course are usable for any type of datasets that contain birthdates and sales information.
So, if you’re looking for a free introduction to DAX and its abilities, this webinar is for you. Want more of a deep dive into DAX? Check out our On-Demand Training platform, with 30+ courses including DAX (Intro and Advanced), as well as Business Intelligence, Business Analytics, Big Data, SQL Server Optimization and more. All our courses go deep into topics and are taught by industry experts; all completed at your pace. Check out our courses with a free On-Demand Training trial today!