DAX calculation types .com
Introduction to DAX IntroDAX concepts are simple and straightforward, but DAX is powerful. DAX uses some unique programming concepts and patterns which can make it difficult to fully use and understand. Traditional ways of learning languages may not be the best approach to DAX, so the goal of this video is to teach you concepts and theory that will help later on in your Power BI work.DAX is a?functional language, which means the full executed code is contained inside a function.In DAX, functions can contain other, nested functions, conditional statements, and value references. Execution in DAX starts from the innermost function or parameter, and works outward. In Power BI, DAX formulas are written in a single line, so formatting your functions correctly is important for readability.DAX is designed to work with tables, so it has just two primary data types:?Numeric?and?Other.?Numeric?can include?integers,?decimals, and?currency.?Other?can include?strings?and?binary objects. This means that if you build your DAX function to work on one type of number, you can be assured that it will work on any other Numeric data.DAX uses operator overloading, which means that you can mix data types in your calculations and the results will change based on the type of data used in the inputs. Conversion happens automatically. This means you don't have to know the data types of the columns you're working with in Power BI, but it also means that sometimes conversion can happen in unexpected ways. It's good practice to understand the data you are using to ensure that your operators are behaving as anticipated.There is one data type in particular that you'll likely be working with a lot in Power BI:?DateTime.?DateTime?is stored as a floating point value with both integer and decimal parts. DateTime can be used accurately for calculations of any time period after March 1, 1900.Video: Where are the hard parts and where to study morePeople coming from Excel tend to write formulas in a single line and do the same for DAX, but no needFormatting: Write the function name and then all the parameters on separate linesDAX is based on columns and each column has a strong data typeOperator overload: write with mixing data types but conversion happens automaticallyAlways put table name before column name in formulaDAX calculation typesThere are two primary calculations you can create using DAX:calculated columnscalculated measuresBefore 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.DAX table and column name syntaxWhether you're creating a new column or measure, it's important to know the general format of table names in DAX:Copy'Table Name'[ColumnName]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:CopyTableName[ColumnName]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:No spaces in table namesAlways include the table name in formulas (don't omit it, even though DAX lets you)Creating calculated columnsCalculated 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:a new column nameat least one function or expressionIf 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.ExampleIts not reporting gross margin per month or anything like that, just reporting margin row by row so this number has no meaningNeed to do a calculation that cannot be down row by row and aggregate laterFirst: need to do the aggregation and then revisionCreating calculated measuresUse 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:a new measure nameat least one function or expressionWhen to use a calculated column vs measureUse calculated column whenever you want the value to have a physical representationAll other scenarios use a measure – possible to never use measure b/c Power BI pre-aggregates for you unlike excel DAX functionsWith DAX, there are many functions available to shape, form, or otherwise analyze your data. These functions can be grouped into a handful of categories:Aggregation?functionsCounting?functionsLogical?functionsInformation?functionsText?functionsDate?functionsSimilar to Excel, when you start typing your formula into the Power BI Desktop?Formula Bar, a list of available functions appears to help you determine which available function you want to select. And by using the?up?and?down?arrow keys on your keyboard, you can highlight any of the available functions, and a brief description is displayed.Power BI displays the functions that match the letters you've typed so far, so if you type?S?only functions that begin with?S?appear in the list. If you type?Su, only functions that?contain?the letter sequence?Su?in their name appear in the list (they don't have to start with?Su, they just have to contain that letter sequence).Limitation: all these functions are able to aggregate a single columnPossible to aggregate the first function but not the second – cannot aggregate expressions(includes not being able to calculate a weighted average)Solve this in 2 ways:Use a calculated column but calculated columns are bad so we don’t want this OR Always 2 parameters: table and expressionAggregation functionsDAX has a number of?aggregation?functions, including the following commonly used functions:SUMAVERAGEMINMAXSUMX (and other?X?functions)These functions work only on numeric columns, and generally can aggregate only one column at a time.However, special aggregation functions that end in?X, such as?SUMX, can work on multiple columns. These functions iterate through the table, and evaluate the expression for each row.Counting functionsOften-used?counting?functions in DAX include the following:COUNT – only for numeric columnsCOUNTA – counts anything but blanksCOUNTBLANK – counts blanks COUNTROWS – rows in a tableDISTINCTCOUNT – performs distinct countThese functions count different elements, such as distinct values, non-empty values, and table rows.Logical functionsThe collection of?logical?functions in DAX include:ANDORNOT – 3 can be expressed with operators AND (A,B) = A && BIFIFERROR – very usefulThese special functions can also be expressed with?operators. For example,?AND?can be typed as (replaced with)?&&?in your DAX formula.You can use operators (such as?&&) when you need more than two conditions in your formula, but otherwise, it's best practice use the function name itself (such as?AND) for readability of your DAX rmation functionsInformation?functions in DAX include:ISBLANKISNUMBERISTEXTISNONTEXTISERROR – not very useful, you should know the type of expressions you are usingWhile these functions can be situationally useful, there is value in knowing the data type of your columns ahead of time, rather than depending on these functions to provide the data type.DAX uses the?MAX?and?MIN?functions to both?aggregate?values, and to?compare?values.Text functionsThe?text?functions in DAX include the following:CONCATENTATEREPLACESEARCHUPPERFIXEDThese?text?work very similarly to the Excel functions that have the same name, so if you're familiar with how Excel handles text functions, you're already a step ahead. If not, you can always experiment with these functions in Power BI, and learn more about how they behave.Date functionsDAX includes the following?Date?functions:DATEHOURNOWEOMONTHWEEKDAYWhile these functions are useful to calculate and extract information from?date?values, they do not apply to time intelligence, which uses a date table.Time intelligence functions will use a different approach, with a date table – people tend to think comparing sales of current year with previous year is possible but its notUsing variables in DAX expressionsUsing?variables?are an extremely powerful part of a DAX expression. – use variable to avoid repeating parts of the expression (also supported in excel 16)The pattern is always VAR defines the variable and RETURN is the result of the expressionYou can define a variable anywhere in a DAX expression, using the following syntax:VARNAME = RETURNEDVALUEVariables can be any data type, including entire tables.Keep in mind that each time you reference a variable in your DAX expression, Power BI must recalculate its value according to your definition. For this reason, it's good practice to avoid repeating variables in your function.leftbottomAlso guarantees evaluation happens only onceTable relationships and DAXPower BI lets you create relationships among multiple tables, including tables that come from completely different data sources. You can see those relationships for any data model in the?Relationships?view of Power BI Desktop.DAX relational functionsDAX has?relational functions?that enable you to interact with tables that have established relationships.You can return the value of a column, or you can return all rows in a relationship using DAX functions.For example, the?TABLE?function follows relationships and returns the value of a column, while?RELATEDTABLE?follows relationships, and returns an entire table that is filtered to include only related rows.The?RELATED?function works on?many-to-one?relationships, while?RELATEDTABLE?is for?one-to-manyrelationships.You can use relational functions to build expressions that include values across multiple tables. DAX will return a result with these functions, regardless of the length of the chain of the relationship.Video: Even though there is a relationship between the two tables you can’t just enter the ‘Product’ table into the expressionFix:Sales: many product: 1 but you want to do the opposite? Not use RELATED but RELATED TABLE# of sales for given product?Create new column ‘NumberOfSales’ but what’s the expression?What if for every product, the amount sold? The most sold?Create a new column: (intermediate step as part of a more complicated expression)DAX tables and filteringOne significant difference between?DAX?and the Excel formula language is that DAX allows you to pass?entire tables?between expressions, rather than being constrained to a single value. One powerful effect is that DAX allows you to filter tables in its expressions, then work with the filtered set of values.Allows you to filter tables down before you do other aggregations – you can apply filters at different scopes and allows you to create entirely new calculated tablesWith DAX, you can create entirely new calculated tables and then treat them like any other table - including creating relationships between them and other tables in your data model.You can iterate without worrying about performanceDAX table functionsDAX has a rich set of?table?functions, including the following:These functions return a full table rather, rather than a value. Typically you'll use the results of a?table?function in further analysis as part of a greater expression, rather than using that returned table a final value. It's important to note that When you use a table function, the results inherit the relationships of their columns.Looking closer at filter:You can mix table functions in your expression, as long as each function uses a table and returns a table. For example, consider the following DAX expression:CopyFILTER (ALL (Table), Condition)That expression would put a filter over the entirety of?Table, ignoring any current filter content.ALL wouldn’t be useful to create a measure but useful for computing percentages (returns same value in all rows)The DISTINCT function returns the distinct values of a column that are also visible in the current context. So to use the above DAX expression example, using?ALL?in that expression ignores filters, while replacing?ALL?with?DISTINCT?would observe them.ALL ignores any filter whereas DISTINCT obeys any filterCounting values with DAXOne common question that Power BI report builders want to answer is the following:How many values do I have for this column?That may be a simple question to answer with a table displayed in front of you, but DAX approaches in a different way in a different way, particularly when there's a relationship between tables.For example, Power BI and DAX includes values that are not properly cross-indexed. If the incoming relationship is broken, DAX adds a new row to the related table that has blanks in every field, and links that new row to the unindexed row to guarantee referential integrity. If your function includes blank rows, such as is often the case when using?ALL, those blank rows will then be included in the number of values returned for that column.DISTINCT does not take into account the blank rows whereas VALUE & ALL doYou can also create entire calculated tables using DAX functions. Calculated tables created using DAX require a?NAME?and a?TABLE?function. Calculated tables can be used like any other table, including establishing relationships.In DAX relationships are part of the model and you don’t need to specify themThe result any table functions can then be used inside modelSo we have new column and we have new measure -- create new tableThe table created in this way belongs to the modelCalculated tables – typically use them to pre-aggregate values, to filter, do summarizationsUseful to test informationWise to normalize a flat file? Depends. Small model works. If already well defined then no. Prefer to use PowerQuery ................
................
In order to avoid copyright disputes, this page is only a partial summary.
To fulfill the demand for quickly locating and searching documents.
It is intelligent file search solution for home and business.