Power BI is an incredible tool for data analysts as it allows advanced manipulation of raw data to generate useful information. One of the crucial elements of Power BI is its ability to create calculated tables. These are tables that are not directly inputted into the program but are instead created with the help of DAX (Data Analysis Expressions). Although they may seem challenging at first, calculated tables can be very powerful when used correctly. In this article, you will gather all the knowledge necessary to build your own calculated tables for your Power BI projects.
What is a Calculated Table in Power BI?
A calculated table is a table that you create in your Power BI model using DAX formulas that calculate the table’s data. Each row in the table is the result of an expression or calculation, and the entire table resides only in your Analysis Service database.
The primary purpose of creating calculated tables is when you want data that’s derived or computed from other data, but you want that new data to be available for other calculations, relationships or reuse across a variety of visuals.
Here is the general formula for creating a calculated table in Power BI:
NewTableName =
FUNCTION(ExistingTableName[COLUMN], “EXPRESSION”)
Where:
- `NewTableName` is the name of the calculated table you are creating.
- `FUNCTION` is the DAX function you would use to create the calculated table.
- `ExistingTableName` is the name of the existing data table in your model.
- `COLUMN` is the column of the current table to be considered for calculation.
- `EXPRESSION` is the DAX formula you wish to apply to the existing column data.
Let us walk through an example of creating a calculated table in Power BI to get a good understanding of how to use it. Suppose, you have a table “Sales” with columns “ProductID”, “ProductName”, “Date”, “UnitsSold”, and “Price”. Now, you want to create a calculated table “ProductSales”, where each row should represent the total sales amount for each product.
Here is how you’ll create it using the SUMX function:
ProductSales =
SUMMARIZE(Sales,
Sales[ProductID],
Sales[ProductName],
“TotalSales”,
SUMX(Sales, Sales[UnitsSold] * Sales[Price]))
In the new calculated table “ProductSales”, there would be columns “ProductID”, “ProductName”, and “TotalSales” representing the total sales for each product.
Conclusion
Calculated tables can help you transform, shape and redefine your data in ways that direct query tables sometimes may not be able to handle. It helps in providing a cleaner, well-transformed dataset for your visuals on Power BI. It’s worth taking the time to learn how to create them – they can add a lot of value and flexibility to your Power BI reports and dashboards.
Remember the key to becoming proficient with calculated tables, like many other features in Power BI, is to practice. As you continue to gain experience and grow comfortable with DAX formulas, your skill in creating sophisticated calculated tables will grow as well.
Practice Test
True or False: Calculated tables can be created by using the New Table option in Power BI.
Answer: True
Explanation: You can find this option under the ‘Modeling’ tab in Power BI Desktop. It allows you to build new tables by defining a DAX formula.
Calculate Tables in Power BI can be created using which of the following options?
- a) New measure
- b) New table
- c) New column
- d) Table tools
Answer: b) New table
Explanation: New table option is used to create Calculated Tables in Power BI.
True or False: A Calculated table allows you to add a column that was previously deleted.
Answer: False
Explanation: Calculated tables are created using DAX expressions and this exercise cannot be used to retrieve previously deleted data.
What is the main benefit of calculated tables in Power BI:
- a) They save storage space
- b) They allow for more complex calculations
- c) They make the report look more organized
- d) They automatically update when refresh the data
Answer: b) They allow for more complex calculations
Explanation: The main benefit of calculated tables is that they allow for complex calculations that can’t be easily accomplished using other methods.
True or False: You cannot filter the data in a calculated table.
Answer: False
Explanation: You can filter the data in a calculated table just like any other table in Power BI.
Which language is used to create Calculated tables in Power BI?
- a) Python
- b) SQL
- c) DAX
- d) Java
Answer: c) DAX
Explanation: DAX (Data Analysis Expressions) is the language used in Power BI to create tables, measures, and columns.
A user can perform which of the following operations on calculated tables?
- a) Add new rows
- b) Delete existing rows
- c) Modify existing rows
- d) None of the above
Answer: d) None of the above
Explanation: A calculated table generated by a DAX formula cannot be altered by adding, modifying, or deleting rows.
True or False: A calculated table cannot use columns from other tables.
Answer: False
Explanation: Using DAX, a calculated table can reference columns from other tables in same data model.
Which of the following is not a feature of calculated tables in Power BI?
- a) They Use DAX formulas.
- b) They can be saved separately as a .pbix file.
- c) They automatically update when data is refreshed.
- d) They can be used to create relationships with other tables.
Answer: b) They can be saved separately as a .pbix file.
Explanation: Calculated tables are part of the report and cannot be saved separately.
True or False: You can’t create correlations between calculated tables and any other table in the database.
Answer: False
Explanation: A calculated table can be used in correlation to any other table in the database.
For Creating calculated tables in Power BI what is the sequence of operation?
- a) Modeling -> New Table -> DAX Expressions
- b) Data -> Modeling -> New Table
- c) Measure -> New Table -> DAX Expressions
- d) Home -> Columns -> DAX Expressions
Answer: a) Modeling -> New Table -> DAX Expressions
Explanation: For Creating calculated tables in Power BI you go to Modeling, select New Table, and then define the table using DAX Expressions.
What will happen if you delete a column that a calculated table depends on?
- a) The column will be automatically restored
- b) The calculated table will update to exclude the column
- c) An error will occur
- d) All calculated tables will stop working.
Answer: c) An error will occur
Explanation: If a calculated table depends on a column that is deleted, the calculated table will fail and an error message will appear.
True or False: Calculated tables do not support drill-through actions in reports.
Answer: False
Explanation: You can create drill-through actions in reports using calculated tables.
Which of the following is not allowed in calculated tables?
- a) Lookup Values
- b) Merge Queries
- c) Relating data from different tables
- d) Creating hierarchies
Answer: b) Merge Queries
Explanation: Power Query Merge operations are only allowed in Power Query and cannot be performed directly in the calculated tables.
True or False: Calculated tables can be created based on the results of measures.
Answer: True
Explanation: A calculated table can be created based on the results of a measure. This enables us to manipulate and enhance the data further.
Interview Questions
What is the purpose of a calculated table in Power BI?
Calculated tables in Power BI are used to generate new tables from existing data within the model. These tables are based on computed expressions and can serve various purposes like simplifying complex calculations or preparing a dataset for analysis.
How do you create a calculated table in Power BI?
To create a calculated table, go to the Data view, select the “Modeling” tab, and click on “New table.” Then, enter a DAX formula to define your new table.
What is DAX in the context of Power BI?
DAX or Data Analysis Expressions is a formula language specifically developed by Microsoft for data analysis and business intelligence. It is used in Power BI for creating custom calculations and tables.
Are calculated tables refreshed when the data in the original table is updated?
Yes, calculated tables are not static. They are refreshed every time the data in the original table is updated or whenever there is a data refresh.
Can you create relationships between calculated tables and other tables in the data model?
Yes, relationships can be formed between calculated tables and other tables in the model using common fields, similar to relationships between regular tables.
What is the syntax to create a calculated table using DAX?
The basic syntax to create a calculated table in DAX is: TableName = DATATABLE (column1, datatype, {{value1},{value2},{value3} ….{valueN}})
What is the role of the DATATABLE function in creating calculated tables?
The DATATABLE function in DAX is used to create a new table with provided column names and data. It can be useful when testing formulas or creating a table from scratch.
Can a calculated table refer to a table which is not present in the Data Model?
No, a calculated table can only refer to tables present within the data model.
Can you use the Calculate function within a calculated table?
Yes, the Calculate function can be used inside a calculated table to modify the context of a calculation.
Are calculated tables stored within Power BI data model as well?
Yes, calculated tables are stored within the Power BI data model as part of the .pbix file.
Are calculated tables processed sequentially in Power BI?
Yes, calculated tables are processed sequentially in Power BI based on the order they were created.
What is a Context in a calculated Table in Power BI?
Context refers to the environment at which the calculations are performed in a calculated table. There are two types: row context and filter context.
Can you create a calculated table from multiple sources?
Yes, using complex DAX formulas, you can combine data from different tables to form a calculated table.
What function can you use to combine rows from two tables into a calculated table?
The UNION function can be used to combine rows from two or more tables into one table.
Are visualizations updated when a calculated table is updated?
Yes, as calculated tables are connected with the data model, any updates to these tables will be reflected in the visualizations.
extutorials.com