Skip to main content

DAX Language - Data Analysis Expression



The DAX language was created specifically for the handling of data models, through the use of formulas and expressions. DAX is used in several Microsoft Products such as Microsoft Power BI, Microsoft Analysis Services and Microsoft Power Pivot for Excel.

Below are the types of Dax functions 

1. Aggregate 
2. Date and time
3. Filter
4. Financial 
5. Information 
6. Logical
7. Maty and trig
8. Other
9. Parent and child
10. Relationship Management 
11. Statistical 
12. Table manipulation 
13. Text
14. Time intelligence 

From the above list of functions 3 types of functions are basic and commonly used, those are Aggregate , Logical and Date and time.


Other important entities which are used with the above function are as follows 

1. Operators 
Example -  ( ), + , Not, &, =, < >, ||

2. Statements 
Define , Evaluate,  Order by, Return, Var

3. Data Types
Binary, boolean,  Currency,  date time, decimal, integer, String , Variant.


In crude language DAX is an advanced version of Excel formulas.
DAX contains similar formulas as in Excel. With dax functions you can filter the data according to specific conditions as we do it with slicers and filter we do in excel.
In DAX data is stored in tabular format. 
There are two Primary calculation you can create using DAX 
1. Calculated column
It means formulas written in columns 

2. Measures 
Formulas written in the area below table.
A formula used to manipulate data is called a measures. 


Advice - I have worked in many automation projects and used Dax functions for past 3 years. While working don't limit your thought process to what you know think what you want i.e the final result. If you don't know the formula but you have a basic idea of the end result then you are on track just search on google and you will get the answers.



Comments

Popular posts from this blog

20 Power BI Dax Measures

Power bi 20 dax measures 20 DAX (Data Analysis Expressions) measures in Power BI with examples: Total Sales: scss Copy code Total Sales = SUM (Sales[Amount]) Average Sales Price: scss Copy code Avg Sales Price = AVERAGE (Sales[Amount]) Total Units Sold: mathematica Copy code Total Units Sold = SUM ( Sales [ Quantity ] ) Total Customers: scss Copy code Total Customers = COUNTROWS (Customer) Total Products: mathematica Copy code Total Products = COUNTROWS ( Product ) Maximum Sales Amount: scss Copy code Max Sales Amount = MAX (Sales[Amount]) Minimum Sales Amount: scss Copy code Min Sales Amount = MIN (Sales[Amount]) Sales Growth Percentage: mathematica Copy code Sales Growth % = ( Total Sales - [ Total Sales Last Year ] ) / [ Total Sales Last Year ] Total Profit: scss Copy code Total Profit = SUM (Sales[Profit]) Total Orders: scss Copy code Total Orders = COUNTROWS (Orders) Total Customers with Sales: css Copy code Total Customers with Sales = COUNTROWS( FILTER ...

What if Analysis

Sometimes, you want to see many different results from various combinations of inputs. Provided that you have only two input cells to change, the Data Table feature will do a sensitivity analysis. Using the loan payment example, say that you want to calculate the price for a variety of principal balances and for a variety of terms. Make sure that the formula you want to model is in the top-left corner of a range. Put various values for one variable down the left column and various values for another variable across the top. From the Data tab, select What-If Analysis, Data Table.... You have values along the top row of the input table. You want Excel to plug those values into a certain input cell. Specify that input cell for Row Input Cell. You have values along the left column. You want those plugged into another input cell. Specify that cell for the Column Input Cell. When you click OK, Excel repeats the formula in the top-left column for all combinations of the top row and left colum...