Write DAX formulas for Power BI Desktop models

DAX is a library of functions and operators that can be combined to build formulas and expression used by Microsoft BI Tools.

Dax is also known as function language, where the full code is kept inside a function.

We can use DAX

1. Power BI

2. Power Pivot For Excel

3. SSAS Tabular Model

4. Azure Analysis Service


By using Data Analysis Expressions (DAX), you can add three types of calculations to your data model:

  • Calculated tables

  • Calculated columns

  • Measures

Date tables

Date tables are required to apply special time filters known as time intelligence. A family of DAX functions, known as time-intelligence functions, only work correctly when a date table is set up. When your source data doesn't include a date table, you can create one as calculated tables by using the CALENDAR or CALENDARAUTO DAX functions.

Calculated columns

You can write a DAX formula to add a calculated column to any table in your model. The formula is evaluated for each table row and it returns a single value. When added to an Import storage mode table, the formula is evaluated when the data model is refreshed and it increases the storage size of your model. When added to a DirectQuery storage mode table, the formula is evaluated by the underlying source database when the table is queried.

Write Dax functions

Formulas are assembled by using:

  • DAX functions

  • DAX operators

  • References to model objects

  • Constant values, like the number 24 or the literal text "FY" (for fiscal year)

  • DAX variables

  • Whitespace
DAX Data Types
Model data typeDAX data typeDescription
Whole number64-bit integer-263 through 263-1
Decimal number64-bit realNegative: -1.79 x 10308 through -2.23 x 10-308 Zero Positive: 2.23 x 10-308 through 1.79 x 10308 Limited to 17 decimal digits
BooleanBooleanTRUE or FALSE
TextStringUnicode character string
DateDate/timeValid dates are all dates after March 1, 1900
CurrencyCurrency-9.22 x 1014 through 9.22 x 1014 Limited to four decimal digits of fixed precision
N/ABLANKIn some cases, it’s the equivalent of a database (SQL) NULL

DAX Operators



1. Logical operators

Use logical operators to combine expressions that produce a single result. The following table lists all logical operators.

Operator           Description
&&    Creates an AND condition between two expressions where each has a Boolean result. If both expressions return TRUE,     the combination of the expressions also returns TRUE; otherwise the combination returns FALSE.
|| (double pipe)    Creates an OR condition between two logical expressions. If either expression returns TRUE, the result is TRUE; only         when both expressions are FALSE is the result FALSE.
IN    Creates a logical OR condition between each row that is being compared to a table. Note: The table constructor             syntax uses braces.
NOT    Inverts the state of a Boolean expression (FALSE to TRUE, and vice versa).

2. Comparison operators

The following table lists the comparison operators, which are used to compare two values. The result is either TRUE or FALSE.

OperatorDescription
=Equal to
==Strict equal to
>Greater than
<Less than
>=Greater than or equal to
<=Less than or equal to
<>Not equal to


3. Arithmetic operators

The following table lists the arithmetic operators.

OperatorDescription
+Addition
-Subtraction
*Multiplication
/Division
^





Exponentiation








100 XP
DAX variables

You can declare DAX variables in your formula expressions. When you declare at least one variable, a RETURN clause is used to define the expression, which then refers to the variables.

We recommend that you use variables because they offer several benefits:

  • Improving the readability and maintenance of your formulas.

  • Improving performance because variables are evaluated once and only when or if they're needed.

  • Allowing (at design time) straightforward testing of a complex formula by returning the variable of interest.

Comments

Post a Comment

Popular posts from this blog

Use DAX iterator functions in Power BI Desktop models

Data Analysis Expressions (DAX) include a set of functions known as  iterator functions . Iterator functions enumerate all rows of a given table and evaluate a given expression for each row. They provide you with flexibility and control over how your model calculations will summarize data. Some DAX Functions are Complex summarization 1. SUMX e.g. : 1.  Revenue = SUMX (Sales, Sales[Sales Amount]) 2. Revenue = SUMX (Sales, Sales[Order Quantity] * Sales[Unit Price] * ( 1 - Sales[Unit Price Discount Pct.])) 3. Discount = SUMX ( Sales, Sales[Order Quantity] *( RELATED ('Product'[List Price]) - Sales[Unit Price] ) ) 2. AVERAGEX e.g :1. Revenue Avg = AVERAGEX ( Sales, Sales[Order Quantity] * Sales[Unit Price] * ( 1 - Sales[Unit Price Discount Pct.]) ) 2. Revenue Avg Order = AVERAGEX ( VALUES ('Sales Order'[Sales Order]), [Revenue] ) 3. RANKX The  RANKX  DAX function is a special iterator function you can use to calculate ranks. Its syntax is as follows: RANKX (<...
Read more

Different charts in Power BI

Image
  An overview of Chart Types in Power BI As everyone might be aware, Power BI is one of the SaaS offerings from Microsoft in the field of BI and Analytics. It provides a very intuitive way for non-technical users to do analyses and create reports on their own without having to rely on a technical team. One of the main purposes of Microsoft behind Power BI is to promote self-service BI and it is one of the BI tools in the market with a comparatively lower learning curve as there are many similarities compared to Microsoft Excel.  In this blog, I am going to give an overview of the most widely used chart types in Power BI.   Let's look at some of the most frequently used chart types in Power BI Bar and column charts Stacked Bar and Stacked Column Chart Clustered Bar and Clustered Column Chart Line Chart Area Chart Stacked Area Chart Line and Stacked Column Chart/ Combo Chart Line and Clustered Column Chart/ Combo Chart Ribbon Chart Waterfall Chart Funnel Chart Scatter Chart...
Read more

Modify DAX filter context in Power BI Desktop models

Summary CALCULATE  DAX function to modify filter context in the formulas. The syntax for the CALCULATE function is as follows: CALCULATE (<expression>, [[<filter1>], <filter2>]…) I learned about filter context. And also learned why and how to work with the CALCULATE function to modify filter context by passing in filters. These filters can add or overwrite the filter context, and they can modify filter context when we are passing in special functions like REMOVEFILTERS or KEEPFILTERS. Additionally, I learned that the CALCULATE function can transition row context to filter context, which can be required when we are using measure expressions in calculated columns or iterator functions.
Read more