In this video, Jeremiah Hersey continues his six-part series on virtual tables in Power BI by diving into an essential concept: data lineage. This concept plays a critical role in how filtering and calculations behave in DAX. Understanding it can help prevent unexpected results and improve the reliability of your data models.
What is Data Lineage?
Jeremiah explains that data lineage refers to the connection between a column in a virtual table and its original source column in the data model. When lineage is preserved, Power BI can correctly apply filter context through relationships. When it is broken, calculations may no longer behave as expected.
Using ADDCOLUMNS with VALUES
The video begins with a simple example using the VALUES function to return a list of product colors. Jeremiah then applies the ADDCOLUMNS function to append a new column called "Amount," which calculates total sales.
- The VALUES function creates a single-column table of distinct values.
- ADDCOLUMNS enhances the table by adding calculated columns.
- Because the column reference remains direct, data lineage is preserved.
This preservation allows the product color column to correctly filter related tables and return accurate sales amounts.
Renaming Columns Without Breaking Lineage
Next, Jeremiah introduces the SELECTCOLUMNS function to rename the color column. Even after renaming, the data lineage remains intact because the expression still directly references the original column.
- Renaming columns does not break lineage if the reference remains direct.
- Filter context continues to work as expected.
- The order of columns may change depending on the function structure.
How Data Lineage Breaks
The turning point comes when Jeremiah modifies the column reference by introducing an expression, such as concatenating a blank string to the color column.
- Using expressions instead of direct column references breaks lineage.
- Once broken, the column can no longer filter related tables.
- This results in incorrect or unexpected calculation outputs.
This demonstrates a key rule: data lineage is only preserved when the expression is a direct column reference.
Impact on CALCULATE Function
Jeremiah emphasizes that the CALCULATE function ignores columns that do not have valid data lineage. This can lead to major discrepancies in results if lineage is broken.
- CALCULATE relies on valid lineage to apply filters.
- Broken lineage means filters are ignored.
- This can cause totals to appear unfiltered or incorrect.
Inner vs Outer Calculations Example
To further illustrate, Jeremiah builds a more advanced example using the SUMMARIZE, ADDCOLUMNS, and SELECTCOLUMNS functions to create a Year-Month table.
- An "Inner Sales" calculation is created using CALCULATE.
- Because lineage is preserved, results are accurate.
- An "Outer Sales" calculation is then created using an additional ADDCOLUMNS layer.
- This breaks lineage because the table is now referencing a derived expression.
- The result is incorrect totals due to lost filter context.
This comparison highlights how subtle changes in DAX expressions can significantly impact results.
Key Takeaways
- Data lineage connects virtual table columns to their original source.
- Direct column references preserve lineage and enable proper filtering.
- Expressions (like concatenation) break lineage and disrupt calculations.
- CALCULATE ignores columns without valid lineage.
- Always verify lineage when building complex DAX logic.
Conclusion
Jeremiah Hersey’s walkthrough provides a clear and practical understanding of data lineage in Power BI. For anyone working with virtual tables and advanced DAX, mastering this concept is essential. By maintaining proper lineage, developers can ensure accurate calculations, reliable filter behavior, and more robust data models.
Don't forget to check out the Pragmatic Works' on-demand learning platform for more insightful content and training sessions on DAX and other Microsoft applications. Be sure to subscribe to the Pragmatic Works YouTube channel to stay up-to-date on the latest tips and tricks.
Sign-up now and get instant access
ABOUT THE AUTHOR
Jeremiah Hersey is a Trainer for Pragmatic Works specializing in the Power Platform. He graduated from the University of North Florida in 2012 and comes with 5 years of teaching experience in high school mathematics. Jeremiah earned the accomplishment every year teaching, of being named one of the Best and Brightest teachers of Florida. His primary focus is helping our customers learn the in and outs of Power Apps.
Free Community Plan
On-demand learning
Most Recent
private training

Leave a comment