In PowerBI, you have the ability to create measures and column calculations in a table.
Column calculations are physical, calculated for each row, which are revaluated when you refresh the data source and stored in the data model (increasing data size and the data model size).
Measures are recalculated to respond to any change in report files and aren't stored. They are evaluated after filters are applied.
The best way to remember the difference is that columns are defining, and measures are dynamic.
For this example, I will be using a dummy dataset that has information about customers and their product purchases.
The tables in the dataset are in the image below.
With PowerBI measures you are able to refer to all tables within one dataset (for measures these column values will need to be aggregated).
For this example, I am in the Sales_Table.
However with columns, only the values in the table you are in will autocomplete (if it doesn't autocomplete then something is wrong!).
How can we solve this issue?
In DAX there is a function called RELATED() which allows you to refer to a column within the dataset when the tables have a relationship.
To see how your tables are related you can see this in the model view.
Make sure to pay attention to the arrows of the relationship as this determines which table you can use RELATED() in.
So if you go into the Product_Table to make a referral column, it doesn't work as the data relationship only works one way as shown in the diagram above.
For this example, I am trying to create a calculated column for the revenue.
In the Product_Table
As you can see, the field has no autocompletion, which means this calculated column will not work.
What about the Sales_Table?
If we make a calculated column in the Sales_Table, the autocomplete appears as the Product_Table data is linking to the Sales_Table
Now I have a revenue per sale on my Sales_Table for further aggregation and analysis!