This page contains the process of generating calculated measures and conditional columns that are meaningful for the business.

DAX Introduction

DAX is the formula language driving the Power BI front-end. It is mainly used to create:

• Calculated Columns - useful to filter & group at a table level
  1. They are sensitive to row context
• Calculated Measures - useful for meaningful aggregations at an individual value level
  1. Measures are sensitive to filter context
  2. Applied filter context flows downstream (Lookup Table → Fact Table), following the design of the data model.
  3. Explicit measures can be used anywhere in the report, but this can’t be achieved with implicit measures.

<aside> <img src="/icons/report_orange.svg" alt="/icons/report_orange.svg" width="40px" />

M-code on the other hand, is a back-end language focused on the ETL process undertaken with Power Query Editor.

</aside>

Applied Steps - Calculated Measures

The step-by-step process for this stage of the project are mentioned as follows:

1. Created a dedicated measures table (Home Ribbon → Enter data → Create Table)

   1. The generated table will be added to the data model, and is also incorporated in Query Editor (back-end)
   2. All newly generated measures with DAX are stored in the measures table

2. Creating a Quantity Sold measure, that sums orders based on filter context.

3. Creating a Quantity Returned measure, that sums returns based on filter context.

4. Creating Average Retail Price measure, that averages the product price based on filter context.

5. Creating a Returns Made measure, that counts the orders returned (irrespective of quantity) based on filter context.

6. Creating a Orders Made measure, that distinctly counts the orders made based on filter context.

   1. The OrderNumber column in the Sales Table is not a unique identifier of a single order {Example: order SO74103}
   2. A single OrderNumber can be repeated if the order contains different products, each identifiable by a unique ProductKey

7. Creating a Customer measure, that distinctly counts the number of customers involved in a transaction.

8. Creating a Return Rate measure by dividing Quantity Returned with Quantity Sold.

9. Creating a Bulk Orders measure to only focus on orders where Orders Made > 1, using CALCULATE( ).

   Bulk Orders = CALCULATE(
       [Orders Made],                       # Previously defined explicit measure '6'
       'Sales Data'[OrderQuantity] > 1      # "Forced" filter context to override the natural context 
       )