Data Analysis Expression (DAX)
Microsoft has introduced a powerful library of functions and operators. Data Analysis Expression (DAX) combines functions to build formulas and expressions in Power BI Desktop. With Power BI’s Data Analysis Expressions (DAX) functionality, data manipulation and data modeling can be done with ease. These expressions are the collection and combination of functions, operators, and constants. Therefore, one formula gives the final result. In like manner, DAX function is similar to Excel functions, while the DAX is much more advanced. If you are aware of the Excel and its functions, you can hand over the DAX easily.
In measures, calculated columns, calculated tables, and row filters use the DAX calculation formulas.
Measures are dynamic calculation formulas where the outcomes change depending upon the conditions. In brief, Measures are used in reporting that help combining and filtering the model data by utilizing different properties. For example, a Power BI report or Excel PivotTable or PivotChart. Measures are created using the DAX formula bar in the model creator.
A formula in a measure can use standard aggregation functions automatically formed by utilizing the Autosum include, for example, COUNT or SUM, or you can customize your very own equation by using the DAX formula bar. Named measures passed as a parameter to the other measures.
Total Sales:=SUM([Sales Amount])
A calculated column is a column that you add to an existing table (in the loaded data in Power BI) and, afterward, create a DAX equation that defines the column’s values. Since a calculated column is created in a table in the data model. Thus they are not supported in models that recover data exclusively from a relational data source using DirectQuery mode.
At the point When a calculated column contains a valid DAX formula, values are determined for each row as soon as the formula is entered. The values are then stored in the in-memory data model. For instance, in a Date table, when the equation is entered into the formula bar:
Calculated Column DAX
Calander = [Calendar Year] & " Q" & [Calendar Quarter]
A value for each line in the table is calculated using the value from the Calendar Year column section (in the same Date table), including space and the capital letter Q. Then including the values from the Calendar Quarter column (in a similar Date table). The outcome for each row in the calculated column “Calander” is calculated immediately and appears, for instance, as 2017 Q1. When closing and reopening a Power BI Desktop file, the table or any related table is processed, refreshed, or the model data is emptied from memory and then again reloaded, Column values are again recalculated.
A calculated table is a table calculated, based on either a DAX query or formula expression, derived from all or part of other tables in the same model. Rather than Querying and stacking values into your new table’s columns from a data source, a DAX formula defines the values in the created table.
Let’s take an example, the Date table, as OrderDate, ShipDate, or DueDate, depending on the foreign key relationship. Thus, by creating a calculated table for ShipDate explicitly, you get a standalone table that is available for queries, as fully operable as any other table. Calculated tables are also useful when configuring a filtered rowset, or a subset or superset of columns from other existing tables. This allows you to keep the original table not damaged while creating changes of that table to check specific scenarios.
Most Used Functions Used in DAX
There are many functions in DAX library. Some of the DAX functions used in Power BI calculated columns and measures are as following:
- VAR( ) & RETURN( )
VAR keyword introduces the definition of a variable. You can have as many variables as needed in a single expression, and each one has its own VAR definition. The RETURN keyword defines the expression to return as an Output.
M_Pos= VAR position = ( SUM ( 'nyc-jobs'[# Of Positions] ) ) RETURN IF ( ISBLANK ( position ), 0, position )
- FILTER( ) & CALCULATE( )
CALCULATE function Evaluates an expression in a context, modified by the specified FILTER functions.
new external vaccancy = CALCULATE ( SUM ( 'nyc-jobs'[# Of Positions] ), FILTER ( 'nyc-jobs', 'nyc-jobs'[Posting Type] = "External" ) )
- REPLACE( )
REPLACE function replaces part of a text string, based on the number of characters is specified, with a different text string.
new_job_id = REPLACE('nyc-jobs'[Job ID],1,3,"TR")
- MONTH( )
Returns the month as in number format (01 for January to 12 for December)
Month = MONTH(job[Date])
- RANKX( )
In brief, the RANKX function returns the ranking of a number in a list of numbers for each row in the table argument.
ranks = RANKX(job,job[month went],,DESC)
- YEAR( )
Returns the year of a date passed as an argument.
Year = YEAR(job[Date])
- GROUPBY( )
GROUPBY function attempts to reuse the data that has been grouped, making it highly performant.
groupby_table = GROUPBY(job,job[Date],"total job on that day", SUMX(CURRENTGROUP(),job[Job Profile]))
- COUNTBLANK( )
COUNTBLANK function returns the number of empty cells in a particular column.
countpreferredblank = COUNTBLANK('nyc-jobs'[Preferred Skills])
- DISTINCTCOUNT( )
DISTINCTCOUNT function counts the number of unique values in a particular Column.
uniquecountjob = DISTINCTCOUNT('nyc-jobs'[Job Description])
- IF( )
IF function returns one value if the condition is TRUE, and returns another value if the condition is FALSE
full_part_time = IF('nyc-jobs'[Full-Time/Part-Time indicator]="F", "Full TIME","PART TIME" )
You can visit our site Loginworks Softwares for consultancy in Power BI and other services
We hope you enjoyed this post; you can share your feedback and your opinion about the article in the comment section below.