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

40 Power Query Editor features in Power BI

40 Power Query Editor features in Power BI along with examples: 1. Filter Rows: Remove rows based on conditions. Example: Remove rows with a null value in the "CustomerName" column. 2. Remove Duplicates: Eliminate duplicate rows. Example: Remove duplicate entries based on the "OrderID" column. 3. Sort Rows: Arrange rows in ascending or descending order. Example: Sort data by "Date" column in descending order. 4. Replace Values: Substitute one value with another. Example: Replace "N/A" with "Unknown" in the "Status" column. 5. Split Columns: Divide a column into multiple columns. Example: Split "FullName" into "FirstName" and "LastName." 6. Merge Queries: Combine data from multiple sources. Example: Merge customer and order data based on the "CustomerID." 7. Group By: Aggregate data based on a specific column. Example: Group sales data by "ProductCategory" and calculate the sum ...

Indirect Function

INDIRECT is pretty cool for grabbing a value from a cell. Can INDIRECT point to a multi-cell range and be used in a VLOOKUP or SUMIF function? You can build an INDIRECT function that points to a range. The range might be used as the lookup table in a VLOOKUP or as a range in SUMIF or COUNTIF . In Figure , the formula pulls data from the worksheets specified in row 4. The second argument in the SUMIF function looks for records that match a certain date from column A. Note: Because each worksheet might have a different number of records, I chose to have each range extend to 300. This is a number that is sufficiently larger than the number of transactions on any sheet. The formula in cell B5 is: =SUMIF(INDIRECT(B$4&"!A2:A300"), $A5, INDIRECT(B$4&"!C2:C300")) Summary: You can use INDIRECT to grab data from a multi-cell range.

Year Over Year calculation using Pivot Table

Instead of creating a formula outside of the pivot table, you can do this inside the pivot table. Start from the image with column D empty. Drag Revenue a second time to the Values area. Look in the Columns section of the Pivot Table Fields panel. You will see a tile called Values that appears below Date. Drag that tile so it is below the Date field. Your pivot table should look like this: Double-click the Sum of Revenue2 heading in D4 to display the Value Field Settings dialog. Click on the tab for Show Values As. Change the drop-down menu to % Difference From. Change the Base Field to Date. Change the Base Item to (Previous Item). Type a better name than Sum of Revenue2 - perhaps % Change. Click OK. You will have a mostly blank column D (because the pivot table can't calculate a percentage change for the first year. Right-click the D and choose Hide.

Solutions for Data Science

Search This Blog