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
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.
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.