How to use DAX to quickly create a sample table

[v-yiruan-msft]

Scenario:
In the forum, an example file is often needed to assist us in describing the problem, but since privacy restrictions prevent the provision of real data, we often choose to create virtual data in Desktop and build a model to solve the problem. This avoids the ambiguity of a literal description of the problem and makes it easier for those who meet the problem to quickly intervene, thus increasing the likelihood that the problem will be solved. It is worthy taking some time to build example data in the long run. This article will show how to quickly create a sales fact table by using the DAX function.

The specific operations are as follows:

1. Create a date dimension table

Measures:

StartDate = DATE(2021,1,1)
EndDate = DATE(2022,12,31)

Create a calculated table with below formula:

Calendar = 
ADDCOLUMNS (
    CALENDAR ( [StartDate], [EndDate] ),
    "Year", YEAR ( [Date] ),
    "Quarter", "Q" & QUARTER ( [Date] ),
    "Month Name", FORMAT ( [Date], "mmm" ),
    "Month Number", MONTH ( [Date] ),
    "Week", "W" & WEEKNUM ( [Date], 2 ),
    "WeekDay", WEEKDAY ( [Date], 2 )
)

2. Create a fact table(For example: FactSales) as below:

• Add date in fact table

  VAR _Date =
      CALENDAR (
          [StartDate], [EndDate]
      )

  
• Add product data in fact table

  VAR _ProductCategory =

  DATATABLE (

      "Product Name", STRING,

      {

          { " Product1 " },

          { " Product2 " },

          { " Product3 " }

      }

  )
  
• Expand the product information: add the sale price and cost price for each product
  

  VAR _DimProductCategory =

      DATATABLE (

          "Product Name", STRING,

          "Sale Price", INTEGER,

          "Cost Price", INTEGER,

          {

              { " Product1 ", 80, 30 },

              { " Product2 ", 50, 15 },

              { " Product3 ", 70, 35 }

          }

      )

  
• Add store data to the fact table:

  VAR _DimStore =

  DATATABLE (

      "Store Name", STRING,

      {

          { " Store1 " },

          { " Store2 " },

          { " Store3 " }

      }

  )

  
• Combine _DimProductCategory and _DimStore

  VAR _DimTable = GENERATE ( _DimStore, _DimProductCategory )

  //Combine _DimTable and _Date

  VAR _FactTableBase = GENERATE ( _Date, _DimTable )

  

  The GENERATE function is used here, and its syntax is GENERATE(<table1>, <table2>), which returns  a table with the Cartesian product between each row in table1 and the table that results from evaluating table2 in the context of the current row from table1.

• Add sold, sales, cost of goods sold, profit and index data to the fact table

  VAR _FactTable1 =

      ADDCOLUMNS ( _FactTableBase, "Sold", RANDBETWEEN ( 1, 99 ) )

  // A separate variable step is added here to fix the [Sold] field.

  VAR _FactTable =

      ADDCOLUMNS (

          _FactTable1,

          " Sales", [Sale Price] * [Sold],

          "Cost of Goods Sold", [Cost Price] * [Sold],

          "Profit",

              ( [Sale Price] - [Cost Price] ) * [Sold],

          "Index",

              RANKX ( _FactTable1, [Date] & [Store Name] & [Product Name],, ASC, DENSE )

      )
  

  The complete formula of Factsales is as follows:

  FactSalesTable =

  VAR _Date =

      CALENDAR ( [StartDate], [EndDate] )

  VAR _DimProductCategory =

      DATATABLE (

          "Product Name", STRING,

          "Sale Price", INTEGER,

          "Cost Price", INTEGER,

          {

              { " Product1 ", 80, 30 },

              { " Product2 ", 50, 15 },

              { " Product3 ", 70, 35 }

          }

      )

  VAR _DimStore =

      DATATABLE (

          "Store Name", STRING,

          {

              { " Store1 " },

              { " Store2 " },

              { " Store3 " }

          }

      )

  VAR _DimTable =

      GENERATE ( _DimStore, _DimProductCategory )

  VAR _FactTableBase =

      GENERATE ( _Date, _DimTable )

  VAR _FactTable1 =

      ADDCOLUMNS ( _FactTableBase, "Sold", RANDBETWEEN ( 1, 99 ) )

  VAR _FactTable =

      ADDCOLUMNS (

          _FactTable1,

          " Sales", [Sale Price] * [Sold],

          "Cost of Goods Sold", [Cost Price] * [Sold],

          "Profit",

              ( [Sale Price] - [Cost Price] ) * [Sold],

          "Index",

              RANKX ( _FactTable1, [Date] & [Store Name] & [Product Name],, ASC, DENSE )

      )

  RETURN

      _FactTable

Summary：

The formula is divided into several variables, some of which can continue to expand and add more data, such as the number of sales by the function RANDBETWEEN () returns a random value, can be nested a layer of IF () or SWITCH () function to simulate the peak and low sales season, or make the data linearly increasing or decreasing. The generated fact table can also be copied elsewhere for quick filtering and changes (note that in Power Query Editor the maximum amount of data to paste into the table is limited to 3000 cells, Excel does not have this limit).

I hope this helps you save time in creating similar sample files, thanks!

Author: Changqing Gao

Reviewer: Kerry Wang & Ula Huang

How to use DAX to quickly create a sample table.pbix