DAX in Power BI

DAX (Data Analysis Expressions) is a formula language with functions which is familiar to writing formula in Excel. We can use DAX for writing aggregate functions, Iterator functions, logical functions and date functions …etc.

1. Iterator functions include: ?Sumx, Countx, Averagex, Maxx, minx, and Rankx?….etc.
2. ?Logical functions include: find, if, and calculate..etc

The CALCULATE function is one of the most important DAX functions in the platform. This function is commonly used, extremely useful, and opens up many doors for your analyses.

Basic Syntax: I like to think about the CALCULATE function in two parts; the aggregation and the filter. The first part of the expression is the aggregation piece. This is where you can place whatever aggregation function you’d like whether it’s SUM(Sales), AVG(Price), or something else.

Here is the DAX syntax for the CALCULATE function in Power BI:?

• ?CALCULATE(<[expression]>,([filter1],([filter2],([filter...]))))

Assume we have three tables names as Cookie Types, Orders and Customers with the following sample data:

Sample input for Cookie Types:

Sample input for Orders:

Sample input for Customers:

Now we are going to answer the following business questions using DAX:

1.????Calculate the total profit for all cookies?

??? Profit= Unit sold *(revenue per cookies –cost per cookies)

2.????What is the weekday we sale the most cookies?

3.????Find all different products that contain chocolate?

4.????How many order did you have for chocolate chip cookies that is sold over 500 units?

5.????How many order did you have for chocolate chip cookies that is sold over the year 2019?

Solution:

Step 1: Importing the data from any data source.

Step 2: Establish relationships to the data model, here in our case, Power Bi by default has created automatic relationship between customer and orders (one to many relationship) so that I have created one to many relationships between cookie types and orders. This means that a single cookies have many orders.

Step 3: After we finalize establishing of the data model, we have to create measure which will run on our data model: ?A measure is a calculation that runs across our data model.

Question 1: Calculate the total profit for all cookies? Unit sold *(revenue per cookies –cost per cookies)).

Question 2: ?What is the weekday we sale the most cookies?

·??????Sunday 1, Monday 2, Tuesday 3------------ Saturday 7.

Question 3: ??Find all different products that contain chocolate?

Question 4: How many order did you have for chocolate chip cookies that is sold over 500 units?

Question 5: How many order did you have for chocolate chip cookies that is sold over the year 2019?

Thank you!