[Power BI] Differences Between Physical Table and Virtual Table
Let’s talk about the differences between “Physical Table” and “Virtual Table”.
Physical Table
Physical table refers to all the tables you see in the data model. That is, when you click the relationship view, all the tables you see here are physical tables.
- Data Model: You can see data model as the tables you import into your Power BI report.
- Relationship View: The bottom icon on the left side of your page. Here you can create the relationship between tables.
How to create the physical tables:
- Import (Direct Query) from your data warehouse / data source (iCloud, Google Query …)
- Create by using DAX function such as SUMMARIZECOLUMNS, ALL.
A DAX created table is also known as Calculated Table. (As the name speak for itself.)
Virtual Table
Virtual tables make the magic happen!
As the name implies, you won’t see virtual tables in the relationship view (because it is virtual). Then why is it important to learn virtual tables?
When (What situations) you should use virtual tables?
Here are some examples that virtual tables would come in handy.
- You have the “Sales Order List by Customers” and you would like to know (1) How many customers bring in sales revenue over $1,000
(2) The total sales that top 10 customers bring in
How to use virtual tables?
It is crucial to understand the use of virtual tables in Power BI. Here I will only discuss how you can identify one. I will have more explanations and elaborations in the future.
- DAX! DAX! DAX! You can only see and use virtual tables in the DAX functions.
- Here are some DAX you use to create virtual tables. Virtual tables are used together with other DAX functions.
(1) FILTER(<Table>,<filter>)
(2) VALUES(<TableName> or <ColumnName>)
(3) DISTINCT(<TableName> or <ColumnName>)
(4) SUMMARIZE(<table>,
[<groupBy_columnName>],…,
[<name>, <expression>],…)
Synonymous (Other Names) for Virtual Tables
You might hear people say “Table Expression” or “Dynamic Tables”. They are all referring to virtual tables.