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.
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.
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.
This preservation allows the product color column to correctly filter related tables and return accurate sales amounts.
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.
The turning point comes when Jeremiah modifies the column reference by introducing an expression, such as concatenating a blank string to the color column.
This demonstrates a key rule: data lineage is only preserved when the expression is a direct column reference.
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.
To further illustrate, Jeremiah builds a more advanced example using the SUMMARIZE, ADDCOLUMNS, and SELECTCOLUMNS functions to create a Year-Month table.
This comparison highlights how subtle changes in DAX expressions can significantly impact results.
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.