In my journey of learning Data Warehouse, I kept learning about DAX, I tried to find some useful DAX commands that can help you day by day.

The commands that worked out are:

  • SUMX, SUM
  • IF
  • FILTER
  • RANKX
  • ALL

 

I used a Portuguese reference to learn about it, so I will explain the context of the exercise and how it works.

The database was made by three tables based in the Star Schema, two dimensions, dProduto (dProduct) and dCliente (dCustomer), the third table is a fact table, fVendas (fSales). Basically is a database of Sales that register sales of products for customers.

In the fact table (fVendas) are all the sales, but there isn’t calculation for anything, so I have the product Id and the quantity that was sold, I did a calculation of the value of that sale (“Valor Venda” column) using SUMX.

DAX Commands

DAX CommandsAfter that I used an IF command to verify if the sale was good or not, if it was less than $4000 (Bad Sale) it shows the quantity of products sold.

DAX CommandsNow the opposite, if the sale value is more than $4000 (Good Sale), show the quantity.

DAX CommandsThen a simple sum to know how much was the sales.

DAX CommandsI’d like to know how many sales were made with Good status and were made with Bad status.

DAX CommandsNow we can see in the Power Table some results about these calculations.

The table shows the name of products and the quantity of sales.DAX Commands

Then if I need to show how many sales were made with Good Sale status? I did it using FILTER.

The FILTER goes through the sales table and filter which sales has the sales more than $4000 and then it sum the total of sales with this status.

DAX CommandsThe opposite of the exercise above:

DAX CommandsNow, what I want to know are the most sold products, so I used the RANKX command!

DAX CommandsThe result “1” looks weird right? But when you see in the Power Table… There is a rank of the sales.

DAX CommandsRight now, you learnt some DAX commands, you can play with the numbers and do some calculations!

Leave a Comment

Your email address will not be published. Required fields are marked *