Welcome to deBUG.to Community where you can ask questions and receive answers from Microsoft MVPs and other experts in our community.
Ask a Question

Introduction to DAX in Power BI

0 like 0 dislike
887 views
in Blog Post by ●13 ●15 ●24
edited by

What's DAX?

DAX stands for "Data Analysis Expressions" which is a library of functions and operators that can be combined to build formulas and expressions in Power BI, Analysis Services, and Power Pivot in Excel data models.

DAX is a powerful query language you can create a full query using it, also you can create custom calculations for Calculated Columns, Measures, Calculated Tables, Calculation Groups, Custom Format Strings, and filter expressions in role-based security in Tabular models. it has multiple functions.

Where we can use DAX?

You can use the DAX in the following:

  • Power BI
  • DAX Studio
  • Analysis Services
  • Power Pivot
  • Excel

DAX Functions

Date and time functions

DAX functions are based on the DateTime data types used by Microsoft SQL Server.

  • CALENDAR
Returns a table with a single column named "Date" that contains a contiguous set of dates. The range of dates is from the specified start date to the specified end date, inclusive of those two dates.
CALENDAR (DATE (2005, 1, 1), DATE (2015, 12, 31))

  • CALENDARAUTO
Returns a table with a single column named "Date" that contains a contiguous set of dates. The range of dates is calculated automatically based on data in the model.
CALENDARAUTO([fiscal_year_end_month])

  • DATE
Returns the specified date in datetime format.
DATE(2009,7,8)

  • DATEDIFF
Returns the count of interval boundaries crossed between two dates.
DATEDIFF([Start Date], [End Date], MINUTE )

  • DATEVALUE
Converts a date in text format to a date in datetime format.
DATEVALUE("8/1/2009")
  • DAY
Returns the day of the month, a number from 1 to 31.
DAY("March 4 2007")

  • EDATE
Returns the date that is the indicated number of months before or after the start date. Use EDATE to calculate maturity dates or due dates that fall on the same day of the month as the date of issue.
EDATE([TransactionDate],3)

  • EOMONTH
Returns the date in datetime format of the last day of the month, before or after a specified number of months. Use EOMONTH to calculate maturity dates or due dates that fall on the last day of the month.
EOMONTH("March 3, 2008",1.5)

  • HOUR
Returns the hour as a number from 0 (12:00 A.M.) to 23 (11:00 P.M.).
HOUR("March 3, 2008 3:00 PM")

  • MINUTE
Returns the minute as a number from 0 to 59, given a date and time value.
MINUTE("March 23, 2008 1:45 PM")

  • MONTH
Returns the month as a number from 1 (January) to 12 (December).
MONTH("March 3, 2008 3:45 PM")

  • NOW
Returns the current date and time in datetime format.
The NOW function is useful when you need to display the current date and time on a worksheet or calculate a value based on the current date and time, and have that value updated each time you open the worksheet.
NOW()

  • QUARTER
Returns the quarter as a number from 1 (January – March) to 4 (October – December).
QUARTER([Order Date])
  • SECOND
Returns the seconds of a time value, as a number from 0 to 59.
SECOND("March 3, 2008 12:00:03")

  • TIME
Converts hours, minutes, and seconds given as numbers to a time in datetime format.
TIME([intHours],[intMinutes],[intSeconds])

  • TIMEVALUE
Converts a time in text format to a time in datetime format.
TIMEVALUE("20:45:30")

  • TODAY
Returns the current date.
TODAY()

  • UTCNOW
Returns the current UTC date and time.
UTCNOW()

  • UTCTODAY
Returns the current UTC date.
UTCTODAY()

  • WEEKDAY
Returns a number from 1 to 7 identifying the day of the week of a date. By default the day ranges from 1 (Sunday) to 7 (Saturday).
WEEKDAY([HireDate])

  • WEEKNUM
Returns the week number for the given date and year according to the return_type value. The week number indicates where the week falls numerically within a year.
WEEKNUM("Feb 14, 2010")

  • YEAR
Returns the year of a date as a four digit integer in the range 1900-9999.
YEAR("March 2007")

  • YEARFRAC
Calculates the fraction of the year represented by the number of whole days between two dates. Use the YEARFRAC worksheet function to identify the proportion of a whole year's benefits or obligations to assign to a specific term.
YEARFRAC("Jan 1 2007","Mar 1 2007")

Filter functions

These functions help you return specific data types, lookup values in related tables, and filter by related values. Lookup functions work by using tables and relationships between them. Filtering functions let you manipulate data context to create dynamic calculations.

FILTER(Sales,<filter>)

Financial functions

These functions are used in formulas that perform financial calculations, such as net present value and rate of return.

COUPDAYS(DATE(2011,1,25), DATE(2011,11,15), 2, 1)

Information functions

These functions look at a table or column provided as an argument to another function and tell you whether the value matches the expected type. For example, the ISERROR function returns TRUE if the value you reference contains an error.

ISERROR(150/0)

Logical functions

These functions return information about values in an expression. For example, the TRUE function lets you know whether an expression that you are evaluating returns a TRUE value.

IF('sales'[product]=12,"Offer","-")

Math and Trig functions

Mathematical functions in DAX are similar to Excel's mathematical and trigonometric functions. However, there are some differences in the numeric data types used by DAX functions.

ABS(-12)

Other functions

These functions perform unique actions that cannot be defined by any of the categories most other functions belong to.

BLANK('Sales'[Coupon])

Parent and Child functions

These Data Analysis Expressions (DAX) functions help users manage data that is presented as a parent/child hierarchy in their data models.

PATH(Employee[EmployeeKey], Employee[ParentEmployeeKey])

Relationship functions

These functions are for managing and utilizing relationships between tables. For example, you can specify a particular relationship to be used in a calculation.

FILTER( 'InternetSales_USD', RELATED('SalesTerritory'[SalesTerritoryCountry])<>"United States")

Statistical functions

These functions perform aggregations. In addition to creating sums and averages, or finding minimum and maximum values, in DAX you can also filter a column before aggregating or create aggregations based on related tables.

COUNT( 'SalesTerritory'[SalesTerritoryCountry])

Table manipulation functions

These functions return a table or manipulate existing tables.

DataTable("Name", STRING, "Region", STRING ,{ {" User1","East"}, {" User2","East"}, {" User3","West"}, {" User4","West"}, {" User4","East"} } )

Text functions

With these functions, you can return part of a string, search for text within a string, or concatenate string values. Additional functions are for controlling the formats for dates, times, and numbers.

LOWER( 'SalesTerritory'[SalesTerritoryCountry])

Time intelligence functions

These functions help you create calculations that use built-in knowledge about calendars and dates. By using time and date ranges in combination with aggregations or calculations, you can build meaningful comparisons across comparable time periods for sales, inventory, and so on.

DATEADD(DateTime[DateKey],-1,year)

DAX data types

DAX supports values for seven data types:

  1. Integer
  2. Real
  3. Currency
  4. Date (DateTime)
  5. TRUE/FALSE (Boolean)
  6. String
  7. Variant

Conclusion

In conclusion, we have briefly explored the DAX and some of its main functions, and when we can use it.

See Also


deBUG Community deBUG.to
If you don’t ask, the answer is always NO!
...