<img height="1" width="1" style="display:none" src="https://www.facebook.com/tr?id=612681139262614&amp;ev=PageView&amp;noscript=1">
Skip to content

Need help? Talk to an expert: phone(904) 638-5743

Data Lineage [DAX Virtual Table Series - Ep. 4]

Data Lineage [DAX Virtual Table Series - Ep. 4]

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.

  1. An "Inner Sales" calculation is created using CALCULATE.
  2. Because lineage is preserved, results are accurate.
  3. An "Outer Sales" calculation is then created using an additional ADDCOLUMNS layer.
  4. This breaks lineage because the table is now referencing a derived expression.
  5. 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

Leave a comment

Free Community Plan

On-demand learning

Most Recent

private training

Hackathons, enterprise training, virtual monitoring