- Ciao, friends.
In this video, I want to talk about data lineage in DAX.
Data lineage is an important concept in DAX
and it is so beautifully implemented
that most developers use data lineage
without even knowing it exists.
The thing is if you want to go to the next level and be able
to write really powerful DAX code,
you need to understand data lineage,
what data lineage is,
how to maintain it and what to do,
in case data lineage, for whatever reason
is not what you need.
That's why in this video,
I'm going to first introduce the concept
of data lineage and then we will see practical examples
of how to use data lineage.
I will not be able to cover all the details
about data lineage.
If you are interested in this kind of topics,
first make sure to subscribe to our channel
and then go to the sqlbi.com website.
There you will find the article related
to this video that goes much deeper
with further details
and further examples of usage of data lineage.
With all that said, let's go straight to the demo.
In order to show you data lineage,
I'm not going to use Power BI at the beginning,
we just use DAX Studio
because I want to show you the result of some queries.
Let's say, for example, that I create a simple query
that evaluates the values of product brand.
That's a bit too large.
Okay.
I run the query and here's the result.
Exactly as you would expect,
I have the values of the product brand.
Now, the important thing
is that these values are not just strings.
They're not just descriptions of brands.
They're actually brands.
The difference between a string and a brand
is that a string by itself cannot filter the model.
A brand, on the other hand,
is able to filter the model.
And we can see that.
If I add a change to this query
and I use ADDCOLUMNS around it
to compute another column,
another column, let's call it amount,
which is sales amount.
Format the code that does not work.
Why?
Okay.
Format the code again.
If I run it, you see that I have different brands
and different amounts.
For each brand, the value of amount
is the sales amount for that given brand.
The reason is values does not return only Contoso
as a string but it returns Contoso as a brand.
Therefore it is able to apply a filter.
I can manipulate the result of a table function
to see if and when data lineage is kept.
So for example, I can create a variable
that I call brands where I just store the result
of product brand.
And then in the return part,
instead of iterating over VALUES,
I iterate over my variable.
If I run the query now,
you see nothing changes
because storing the result
of a table function in a variable
does not break the lineage.
What if I apply further calculations?
For example, I might be interested
in getting rid of Contoso.
So I can create a new variable
brands no Contoso where I use FILTER over brands
where the product brand, product brand
is different than Contoso.
Now brands no Contoso is the result of another
table function, which is running on top of a variable.
The question is whether data lineage
will be maintained or not.
I use brands no Contoso here.
I run it and you see that Contoso
is no longer here
but still, the values are correctly filtered.
So data lineage is still maintained.
What if I change the name of the column?
Instead of using FILTER,
I can use another variable, brands new name
where I use SELECTCOLUMNS,
SELECTCOLUMNS from brands,
my new brand name which is
product brand.
Product brand.
Okay, now I have a new variable, brands new name.
I can use this variable here,
run the query and now you see the column name
is different but still the filtering is happening.
So even though I renamed the column,
the lineage is maintained and my column
is still able to filter.
What if I do something different?
Instead of just renaming the column,
I use an IF statement.
Before I removed Contoso, I want to remove Contoso again.
So IF Contoso,
if it's Contoso, then we write no Contoso,
otherwise the product brand.
Let's format the code.
And reduce the font a bit.
Okay, nah, too much, that way is better.
So I rename the column but most important,
now I use this complex expression.
So the result will not contain Contoso.
Instead of Contoso, it will contain the string no Contoso.
Let's run it and look at the results.
Now you see what happens.
My new brand name is no longer able to filter the model.
I see no Contoso
but the amount is showing the grand total.
The reason is I broke the data lineage.
If you keep data lineage,
until you keep data lineage,
the filtering still happens the right way.
But if you use expressions
or you use calculations on top of columns,
then the engine is no longer able
to understand from which column your expression comes from
and data lineage, at that point, is broken.
Now, what to do if you want
to restore the original data lineage?
There is a function that does it, which is TREATAS.
Right now, brands new name is not able
to filter the model and you see it here.
But what if I want to restore the data lineage?
I can use the TREATAS function.
Let's call brands new name correct lineage.
TREATAS requires you to specify, TREATAS
lets you change the data lineage of a table.
So we have a table with one column only.
That is the reason why we need to provide the type
of the only column, which is contained in our table.
We want to change the data lineage of brands new name,
and force it to be a product brand.
So TREATAS changes the data lineage
of brands new name with ...
with the original data lineage,
which is that of product brand.
Now let's go for our variable,
brands new name correct lineage.
Now, focus on this row.
Right now, I'm showing no Contoso
and I'm showing the grand total everywhere.
If I run the query now, look what happens.
I no longer see Contoso,
I no longer see no Contoso
but I see a correct value for the amount.
This is what TREATAS does.
TREATAS restores the data lineage
of a table but while doing that,
it might ignore rows if the value contained
in that row does not represent a valid product brand.
So as you have seen, you can keep data lineage
as long as you reference columns
and use basic table functions.
But if you start using expression,
data lineage might be broken
because an expression cannot be tied
to a column in the model.
If that happens and you need to restore data lineage,
you have the option of using TREATAS
in order to restore the original lineage.
Now, let's take a look at a practical example
where TREATAS and data lineage are important
for the performance of a formula.
To do that, we need to go to Power BI.
I have this report where I'm showing the brand,
the sales amount and then I have a measure, first day sales.
First day sales computes the sales
for only the first day when a product was sold.
So a product starts selling on a given date
and then for a long period has sales
but I'm interested only in the first day.
To do that, first day sales iterates over the product,
computes for that product, the first sale ever
and then computes the sales amount only in that day.
The filter on date date forces the date
to be only the first one.
And SUMX iterates over product,
so this CALCULATE through context transition,
computes the sales of that product in that day only.
The numbers are fine and you can see the result here shows
some values, which are the sales only in the first day.
The problem of this formula are performance
because SUMX iterates over product.
Let's say we have one million products
and then for each product,
we need to compute the first sale
and then scan the sales table
in order to compute the value.
That iteration over product
is running once per product.
So if you have, well, one million
was probably too much but 100,000 rows
then this can be 100,000 iterations
and for 100,000 times, I need to compute sales amount.
If you have 100 million rows in sales,
that means that the complexity
of this calculation is... 100 million.
Quite a large number.
What we want to do is find a different way
of computing the same expression.
What if, instead of computing the value this way,
we first compute a table containing the product key,
the first date and we use this table as a filter?
That will reduce complexity by a lot
and it's actually easier if we try to do that together.
Let's go back to DAX Studio.
Let me remove everything.
What I want to obtain is a table
that contains the product and the first sale.
So I just run EVALUATE.
I can use SUMMARIZE.
I SUMMARIZE sales by product
by product product key.
And if I run this, I get only the product keys.
That is fine.
I now have the product keys, I also want the first sale.
So I can add a column.
ADDCOLUMNS to this result
and I create... a new column first sale
which is CALCULATE...
CALCULATE MIN
of sales order date.
Okay.
MIN, probably there is a space here.
Okay.
So now I have a table that contains the product
and for each product, the first sale
when that happened.
I can use this as a filter in CALCULATE
so that in one step, I will be able to filter
both the product key and the date.
So I can store this in a variable, let's call it
products and first date.
Okay, I'm just fixing the syntax.
Now I'm using a variable
just to be able to format it.
Now I have this number
and I can use that as a filter.
So I can create a measure.
Yeah, I can create a measure.
No, I need to do that in ADDCOLUMNS,
so let's write a query.
ADDCOLUMNS to the values of product brand.
And I want first sale date,
which is CALCULATE
I actually need to take these,
yeah, actually it's easier to do a variable.
Let's go for DEFINE MEASURE,
DEFINE MEASURE sales first sale date
where I define my variable
and then I use here,
RETURN CALCULATE
sales amount and I use my products
and first date measure.
So here I can reference
my first sale date measure.
Let's check that I wrote it correctly.
I did not.
So where is my problem?
DEFINE MEASURE.
Ah, that needs to be EVALUATE.
Okay.
I want
this and I don't need all this space.
So let's review the code.
Products and first date will contain my table
that contains the product key and the first sale.
Then CALCULATE uses this as a filter
and computes a sales amount.
And I'm iterating over the product brand,
computing the first sale date.
I'm iterating over product brand
because in my report, I already have the result.
I have 401 that is the value for A. Datum.
So what I expect is that if I sort it,
ORDER BY brand,
I expect to see the value of A. Datum.
Actually, what I see is 2,096
which is not 401.
And if I go in my original report,
you see that this is the entire value of sales amount.
So it's likely my measure is not working
and we need to understand why, what is happening?
What is happening is that we have a problem
with data lineage.
Let's analyze the data lineage.
My products and first date,
despite containing a product key and a date,
it does not have the correct lineage
because for the product key, it has a product key.
But when I compute first sale
I use an expression and as an expression,
it does not have the correctly lineage.
It has a new lineage, which is created only for the column
during the execution of a query.
So it's not able to filter the model.
But I can change that.
What I can do is force the lineage of this variable.
Let's call it products and first date
with lineage where I use TREATAS.
I change the lineage of this variable.
Now I have two columns,
so I need to provide the first column,
which is a product key.
And for the second column that is a date,
I want it to be a date date.
Date date.
Now, TREATAS returns the same result
but now the data type is the correct one.
So I can use this variable now
instead of the previous one
and if I run the code now,
you see the result is different.
401, 969.
Let's go back, we have 401, 969.
So we have been able to compute using data lineage
the same expression that was previously
computed through a SUMX with just a simpler scan
because that measure requires one scan of the sales table
and another scan of the sales table.
So it is much faster in the execution
and it will provide a better user experience.
Now, as you have seen,
data lineage is important.
It's easy to understand what it is.
It's easy to understand when it is maintained
and it's also easy to force data lineage
to what you need.
The only tricky part of data lineage
is being able first, to recognize
that a measure that is not computing the correct value
is doing that because of a problem with data lineage.
Once you understand that
and once you figure out that data lineage
is always maintained for column references
and it's always broken whenever you use expressions,
then you can use TREATAS to force the data lineage
that you need in your expressions.
Enjoy DAX.