Nowadays, DATA is generated at astronomical rates. The trend of gathering & analyzing data has become essential to every business to improve decision-making.
Power Bi is a Business Intelligence tool that facilitates Data Management. What makes Power BI stand out is that it contains advanced analytics abilities that range from data analysis to machine learning. These types of analysis are based on a specific language called DAX (Data Analysis Expressions).
This article highlights Formulas & Dax queries in Microsoft Power Bi.
What are DAX Functions?
Data Analysis Expressions (DAX) is a formula expression language used in Analysis Services, Power BI, and Power Pivot in Excel. DAX formulas include functions, operators, and values to perform advanced calculations and queries on data in related tables and columns in tabular data models.
It has all the functions and formulas needed for any type of analysis.
DAX library has the following key features:
- Measures: DAX Functions contain formulas called Measures to carry out dynamic calculations.
- Calculated Columns: With DAX you can create Calculated Columns to store computational results of your data operations.
You can learn more about DAX here.
Must learn DAX Functions for Power BI
Date and Time
- DATE: Calculate a pre-specified date as output in datetime format:
DATE(<year>, <month>, <day>)
- DATEVALUE: Convert the format of the date from text to datetime:
- TODAY: Shows the current date as output:
- WEEKDAY: Calculate a number ( 1 to 7) corresponding to the day of the week of a date:
- HOUR: current hour in the form of a number ( 0 for 12:00 A.M. and 23 for 11:00 P.M.) :
- CONTAINS: This return a Boolean value ( True OR False)
CONTAINS(, , [, ,]…)
- CUSTOMDATA: Returns the content of the CustomData property in the connection string:
- LOOKUPVALUE: Returns the value for the row that meets all criteria specified by one or more search conditions:
LOOKUPVALUE( , , [, , ]…)
- USERNAME: Returns the domain name and username from the credentials given to the system at connection time:
- ISBLANK: Checks whether a value is blank, and returns TRUE or FALSE:
- AND: Checks whether both arguments are TRUE, and returns TRUE if both arguments are TRUE. Otherwise returns false:
- NOT: Changes FALSE to TRUE, or TRUE to FALSE:
- OR: Checks whether one of the arguments is TRUE to return TRUE. The function returns FALSE if both arguments are FALSE:
- SWITCH: Evaluates an expression against a list of values and returns one of multiple possible result expressions:
SWITCH(, , [, , ]…[, ])
- IF: Checks a condition, and returns one value when it’s TRUE, otherwise it returns a second value:
Mathematical & Trigonometric Functions
- ABS: Returns the absolute value of a number:
- ACOS: Returns the absolute value of a number:
- CEILING: Rounds a number up, to the nearest integer or to the nearest multiple of significance:
- COMBIN: Returns the number of combinations for a given number of items. Use COMBIN to determine the total possible number of groups for a given number of items:
- CURRENCY: Evaluates the argument and returns the result as currency data type:
- ADDCOLUMNS: Adds calculated columns to the given table or table expression:
ADDCOLUMNS(, , [, , ]…)
- AVERAGE: Returns the average (arithmetic mean) of all the numbers in a column:
- AVERAGEA: Returns the average (arithmetic mean) of the values in a column. Handles text and non-numeric values:
- BETA.INV: Returns the inverse of the beta cumulative probability density function (BETA.DIST), If probability = BETA.DIST(x,…TRUE), then BETA.INV(probability,…) = x. The beta distribution can be used in project planning to model probable completion times given an expected completion time and variability:
- CHISQ.INV: Returns the inverse of the left-tailed probability of the chi-squared distribution.The chi-squared distribution is commonly used to study variation in the percentage of something across samples, such as the fraction of the day people spend watching television:
- BLANK: Returns a blank
- CODE: returns a numeric value(code) corresponding to the first character in the input text string:
- CONCATENATE: Joins two text strings into one text string:
- EXACT: Compares two text strings and returns TRUE if they are exactly the same, otherwise returns FALSE. EXACT is case-sensitive but ignores formatting differences. EXACT is case-sensitive:
- FIND: Returns the starting position of one text string within another text string. FIND is case-sensitive:
FIND(, [, [, ]])
Conclusion: Microsoft Power BI DAX Functions
This article listed the MUST LEARN DAX functions to have a full grasp of Power BI’s capabilities, So what are you waiting for?
Start your free trial today with Windsor.ai and start playing with the functions we just learned together!
You May Be Also Interested in:
- Free Power BI Templates in 2023
- Connect Salesforce to Power BI
- Connect Twitter Ads to Power BI
- Connect Snapchat Ads to Power BI
- Connect Bing Ads to Power BI
- Connect Zoho to Power BI
- Connect Linkedin Ads to Power BI
- Connect Klaviyo to Power BI
- Connect HubSpot to Power BI
- Connect Instagram to Power BI
- Connect Tik Tok Ads to Power BI