thank you everyone for joining today we
will be continuing everyone's
presentation and learned ex parte - I
guess without wasting any time I'll give
Jeffrey the mic and the floor
okay thanks Jason we'll continue from
last time this is the last slide I
talked about at the end of the last
presentation and today we're going to
continue to go over bags last time we
went over the short history and the the
main concepts index the row contacts and
the filter contacts and the kite
different categories of tax functions
and the different the usage of the axion
as measures calculated columns calculate
tables and the role of security
expressions and the important calculator
function that allows you to manipulate
the filter contacts and the convert from
low contacts to filter contacts today we
will be going over some common examples
especially some of the common issues tax
users tend to run into I'm going to show
you some some patterns and that and and
what are the pros and cons of each
approach I know
we reserved about a two-hour for this
presentation but I don't think we need
that much time it's we reserved two
hours just in case we need a little bit
extra time I think that one hour should
be sufficient so let's go to so as we
all know the most the most powerful
feature of tax is to create a measures
right measure is a dynamic expression
that can be calculated on the fly in the
report layer that can react to all the
filters is set by the users and the
calculated function is one of the
primary functions for people to write
tax measures because it is used to
change the filters that's set by the
users and let's first look at a very
simple tax calculator statement the
purpose here is simply to say I want to
calculate the total sales one country of
a unit in the United States right so
regardless whether filters are on the
report I want to calculate the total
sales in the units in the United States
so here are the two common ways I see
people do this
one way is you say calculate sum of
sales amount where the country equals
United States this is one way to do it
and here's another way I see that users
are often right that is to write
calculate the sum of sales amount filter
the geography table under the country
equals United States and I seen people
arguing about which way is better and
let's see and that are the equivalent
and they seem to be equivalent in most
common cases so let's see how they
actually work in a report so let me
switch to the PD IX file I've created
beforehand then you see mypbx file yes
okay that's great okay so in this simple
report I have created the a couple of
measures the first one is simply total
sales as the sum of sales amount and
then the second measure is I'm using the
first syntax that is here I have to
switch to here
so I'm using the first way to do it
that is simply saying geography country
equals United States so this is the
first way for me to set the filter and
then I created another measure Us sales
- and no and I'm using the second common
way of setting the filter filter
geography table column equals United
States so if I add this two measures to
a report and here if I simply in the
report I'm showing for all the different
years thus the total sales in that year
I can add the US sales one to it it
works it shows me the sales in the
United States in different years I can
add the second way of doing the same
thing US sales - I'm getting exactly the
same numbers so that's why I've seen
many tax users using NACA depending
their personal preferences using one of
the approaches to set a simple fail with
her in a tax measure but in reality
these two ducts measures have a
different semantics they are doing
different things so let's go to the
second report in the second report I'm
showing you the total sales for
different countries so you can see all
six on six countries under the United
States sales is about nine million nine
point four million dollars so now let me
add the same two measures to this report
first let's add the US sales one and
that's working as expected
right so with the regardless of the kind
of filter which means that regard is
whether the current country is I owe
this measure always on the return
the US sales which is exactly roughly
nine point four million dollars so what
if I added a second measure to it and
how come a the army returned the sells
for for the filter is United States I'm
only having the value for the United
States and for all the other roles
they're all blacks so that tells us that
these two measures are actually behaving
differently and before we switch back to
the slides let me show you a third way
which is actually equivalent to the
first way at the first way which is how
the unit as US sells 1.1 if you look at
this expression
it says filter all of geography country
where country equals United States so
this third way is also using the filter
function just like the second way but
instead of geography is saying all of
geography country where country equals
United States
so before I live let me first add the
third measure to the second report where
it doesn't work you can say okay the
third measure works exactly like at the
first measure right it returns the cells
in the United States regardless of
whether the current country is does it
also work in the first report or over
different years yes it does so all three
measures return the same results when
I'm grouping by year but they does the
second approach returns many people say
wrong results when I'm grouping by
countries instead of a year so let's go
back to the slide okay so what's the
difference here as it turns out tax
in index there are many expressions that
are filtered by the by the filter
context okay so what type of the
expressions are filtered by the filter
context and here I'm showing you a few
examples there are many other examples
okay so for example if I say count rows
of geography where geography is a table
name then the the yellow the highlight
in the part is filtered by the filters
in the filter contacts so the geography
country the geography table here is not
a food geography table is not all the
roles in the geography table but rather
is the future the rows of the geography
table which filters it depending on
whatever filters you put on the report
and and in the in the in the previous
example if the filters are year then
those filters have no effect on country
because the filter is from the data
table and the data table does not filter
the geography table therefore those
filters even though they're present they
have no effect on the geography table
but in the second report when you have
countries as a filters then countries do
filter the geography table
so so so the simple fact of a writing
attacks table expression by the self a
table name by itself does not mean I'm
I'm fetching all the rows of this table
but rather I'm fetching all the filter
rows of this table and the second coming
example is I can use distinct of a
column name for example if I say color
rows of distinct the geography country
what does it mean here is I want to
gather the distinct values of the
country column after the filters are
applied some first a filtering the
geography table and then for the
surviving rows I'm getting the distinct
the list of countries
and finally the counter OHS function
will just return the count rows of
basically this whole the full expression
is returning the filtered list of
countries that the number of a filtered
list of distinct countries similarly the
value function the value of a column
that's pretty much the same thing
so values of geography country means
give me filtered list of distinct
countries and then I will count the
number of them the difference between
values and the distance I'll talk about
in the following slide here's another
very common disease Russian has one
value geography country the legal means
after you apply the filters to the
geography table get the list of distinct
countries to see if there's exactly one
country left after the filters are
applied the Eastfield heard of a column
name means do I have a filter directly
on this column I'm just it's basically a
way for you to detect whether there is a
directed filter on this column by the
way index many people says why do we
have the distinction between the
aggregate functions under the X version
of the aggregate functions for example
some have some ax and Ming and min x max
max ice content contacts some people say
all the some functions are more
performant than the ax functions
actually some is a simply syntactic
sugar for the X version so some of a
sales amount column is literally
equivalent to its ass indexer for some
acts of the sales table comma and then
the expression is a sales amount column
because the sales table is a name table
named by the self as I've said that
means it's it's not the full sales table
but as a filter the sales table that's
why some if you're
if your measure expressions are simply
sum of sales sales a month then it will
return the filtered sum of sales and
Matt that is exactly because everything
in order directed table naming attacks
expression it's actually means a filter
the table for so for this reason if we
go back to our original expression one
two three
the the second expression filtered
geography country C equals United States
the geography here you can see it's a
table name by itself that's when it's a
filled her table so the disc geography
table is actually the filter the
geography table while the pre the first
expression geography country equals
United States is equivalent index to the
third expression filter all of her
geography country where country goes
United States and the other the key word
here is all all means unfiltered so why
say oh of geography country that means
give me a distinct list of countries
from the original table without any
ignoring any filters on this so that all
of a column means unfiltered color while
the filter geography comma country goes
United States means a filter the table
under then country equals United States
and that the first expression is a
syntax sugar for the third expression so
it's equivalent the two with all around
that the column name therefore it's the
unfiltered country so with this
knowledge if we go back to see the
report it now makes sense why in the
third report that why I the
you know us cells to many of the row
values are blank is because in this case
because the expression in the self is a
filter the geography so the geography is
a first a filter by the current country
which is Australia so in this robe when
I'm calculating this measure at first a
filter the geography table to Australia
and then
I'm gonna do a additional filter on top
of the previous filter the results which
is I want to say for any surviving rows
I want to further filter by country
equals United States so this double
filter producer empty results because
you cannot be the country he goes to
Australia and also country equals United
States as a result the filter expression
producer empty filter therefore you get
a empty sum of sales amount similarly
you get an empty for Canada empty value
for France Germany United States and you
only get a value for United States
because the United States will first a
filter the geography and then you get
all the rows belong to United States and
then you filter it again which is a
united country equals United States so
it it doesn't change anything so the
final result of this expression filter
geography karma country goes United
States for this particular role is all
the cities in this in the United States
and then this new filter is used to
filter the sales table that's why you
get the sales in the United States for
this row where for the other two
measures
it's basically says regardless of what
the current current the filter is I'm
gonna use the unfiltered United States
together United States under then I'm
gonna replace this filter so so for the
for the first measure under the third
measure they work because first they
produce a filter expression for country
equals United States regardless of what
the current filter is and then the new
filter is used to overwrite the existing
filter to replace it by by the new
filter and then that's why we get
ourselves in it in the United States
regardless of what the car in the
country filter is that's how it works
so let's go back to the slide so as I
said index if you put all around a
country name where all around a column
name and all around the table name or
all around a column name that means the
unfiltered the table where the
unfiltered kana and the way you set a
filter sometimes you you do want that
the double filter to you do wander the
double filter semantics but for many
users if you are not sure which
semantics you need do I need that the
the all semantics were I needed a filter
the semantics if you are not sure always
use the hour because that should be your
default option this has the minimal
impact right first of all is the disk is
Russian it can be evaluated independent
of the existing filters on your report
so they tend to be fast you know says
right because you're evaluating
expression regardless of what the kind
of filter is well this expression you
have to say this filter actually changes
based on which row you are on so we have
to evaluate a different filter a
producer different the results and then
use the different the results to
calculate the sum that that's more work
therefore can be more expensive to do
while this one is a independent the
expression it doesn't care whether the
current the filter is I don't always
produced country equals United States
and then it will be used to override
whatever the current filter is to
calculate this sum so you can think
about it even semantically this is a
cheaper expression to do and also it has
the minimal side effect many people
wouldn't know that this expression
actually returns all the columns in the
geography table not only the country
column because because when I say
geography is a table name and by default
an index table name include all the
columns in this table and and it can be
very expensive and also it can be has
many side effects will have a so many
extra columns while this is Russian only
returns a single
because I'm getting the list of
individual countries so this expression
tend to have a minimal side effects
compared to the this expression and
there so if you are not sure which one
you want I would just use the the first
expression which is a shorthand simply
say condom equals of bad that has
minimal side effect and that you should
only use the second one we already know
that you wanted this double filter
semantics let's look at another example
and the data when you write a filter
expression that actually is filtered is
itself by the surrounding filters in the
report it can have a performance
consequences let me show you some
concrete examples both expressions I
have seen from actual user reports so
the first expression is y user say on my
report I want to select a product but
then I want to count all the customers
who bother the other products so they I
want to I want to get at the count of
customers
now that who bothered the product I said
like that but the who part of the
product I didn't select who bothered the
other products so he came up with this
expression but there are different ways
to do it and the depending on the
configuration worth I'm just saying I'm
didn't say this is the most efficient
expression I'm dissing this is one way
to do it and the for a reasonable size
the model it works just fine
so this what is a user came up with was
I want to calculate it by the way the
user wanted this thing cut off customers
so the user says I want to get the
distinct count of a customer key column
and then he used the tax except
expression which is like the set at the
subtraction I first get all the products
under then he cleverly used the table
name by itself we already know that the
table name by itself means a filtered
right so I want to get to the quantity
filtered product which means currently
selected the product so I want to use
the all product to subtract it to
- the selected product what does this
whole thing give us it gave us the
unselected products so basically
anything other than the selected the
products and then I want to get at the
kind of customer who bought those
products so this is pretty smart
expression it's also fully dynamic and
that the second the expression is one
user he wants to say I want to get the
distinct honor customers who bought some
high-cost product so the way together
the high cost product is they want to
get at the passerby the way he want to
get to selected the high cost products
so he has some filters that can narrow
down the list of products so that's why
he used the values of a column function
the values of economy means I want to
get the selecting the products and then
for their cost I want to say it's a high
cost
that's a greater than certain number and
then I want to get the distinct the
customers who bothered those products so
I'm gonna go back to the desktop to show
you how the first expression work but
the post expressions I want to put it
here to show you examples where the
filter expression is actually dynamic
the filter here the the first part is
the accept the function the second
argument is a table name by itself that
that means is a filtered product and the
second calculated expression the values
of a con function means future column
suppose our dynamic of my new filters
and in this particular case because the
measure self is doing that distinct cont
it has some performance consequence
right and so that means I want to get
the distinct honor for this filter and
the since the filter itself can change
depending on the current filter on the
side then we will see that the
computation can become quite expensive
depending on the size of your model so
let's go back to the desktop
so here I created a few measures and so
the first measure I said a simple cut
notice that this is a calm not distinct
I'm ok I'm just getting the kind of
customers another measure is a number of
customers other products means I'm
getting a count not a distinct count of
I'm using the acceptor pattern to say I
want to get a count of customers who
bought that the other dozen on selected
products under then another measure this
time I'm getting the distinct hunt of
customers this one I did in the change
filters therefore this is the distinct
count of the customers who bought the
selected products under the force
measure similarly is I'm getting the
distinct count of customers who bought
the unselected products so if
effectively the four measures is come of
customers who bother selected products
count of customers who bought the
non-selected come products distinct
count of customers who bought the
selected the products and distinct count
of customers who bother on selected
parents and in the first a table you can
see I get the let's say my report
showing the different color products
right so the four different counterparts
I want to say how many customers this is
a who bother each colored product and
how many distinct the customers who
bother the product of that car you can
say that this thing the county is a less
than the top that makes sense because
it'll remove duplicates so now let's see
what if I used the other measure that
the other two measures was using the
kind of customers who bother the other
basically nowadays color and if I added
it to the first report I'm gonna get a
for the first a row
I'm gonna get the kind of customer who
bought a non-black colors that the who
bought part which is not a black color
and as a nod blue color not a gray color
okay for the third report I'm gonna add
that these thing kind of customers who
took the distinct of customers who
bothered a non-black and now blue none
great the cottage so the the the middle
one is the that the the the right
one is that distinct um let's see how
they're the difference in their
performance remember in this case the
filters will change right because as I
said that the filter will change from
color to color this color is on the
product table therefore it will filter
whatever the kind of selected the set of
products will be and they sewed a filter
expression that the exact accept
expression will change from low to low
before they'll do that
I've already connected a single Server
profiler to this report so I can see
some under the hood queries generated by
the tax engine to calculate the post
expressions so I'll go back here and
then let me clear the trace so that we
can focus on the new query
so let's first go to the mid table this
the first one I'm going to add is the
kind of customers who bothered the other
products I did here it's pretty fast and
if I look at the trace so there are two
queries other than a bunch of other
birdie par queries a lens in the middle
you don't have to know all the details
the two queries you can actually kind of
I understand what the means is let me
zoom it in so it's using a tax
expression called the summarized columns
if for people who are already familiar
with this one
they literally show you the list of
customers the list of filter the list of
a group of columns the list of filters
and the list of measures you want to
calculate and in this particular case I
don't have any filter I just have a one
column the product color and the one
measure number of customers who bought
other products and that the Verde Park
engine is the the verdict mark as the
acquiring events all these events are
the number of internal queries the tax
engine have to send to the verde package
in order to produce the final output and
you don't need to know the the exact
meaning of them but you can just have a
general idea that we said that roughly
you don't have to worry about this ten
and attend the sub class those are the
sub events so you only have to look at
this is zero zero zero zero so in order
to calculate a data query the tax
engines and the for birdie packages
where the bad engine queries each
vertebrae engine quarter you can think
about is a table scan okay so now we
need to actually do a scan of the table
in order to produce the result and now
let's go to the second one so let's go
to the third report and this time
instead of adding the count of customers
who bought the other color products I'm
gonna do the distinct count of customers
it's a super fast and that the numbers
are slightly smaller which makes sense
as always distinct customers should
remove the other case of the customers
and but if you actually look under the
hood you can say wow that we see a lot
more very packet and jinn demons a lot
more and the query itself if you look at
the tax query it's still using summaries
condoms and has only one column on the
report which is a prior color and then
one measure that is a distinct number of
customers who bought the other
and why there are a lot more events is
simply because a tax engine is pretty
smart in the sense that the is a
additive thing by additive it means I I
because the report will change a firm
product the color to park honor but the
deduct from Harvey to product but the
tax engine is a smart enough to say okay
I can lower the granularity send a
single query to fetch all the different
products because the product filter will
change of Rama from row to row but I can
just go down to them for example I can
go down to the product the key level and
again all the products and under this
and they load up their selves and then I
go back to the tax engine I would
dispatch those results in two different
colored buckets and then I can do an
aggregation after of the result for each
bucket but for this think on with no
longer have the luxury because the
distinct entities you can now they use a
partial aggregation distinct and you
have to go to the leaf level right you
have to remove duplicates so four
distinct under the tax engine has to
send multiple queries back to the very
back engine you just have to get a
different at least income so you cannot
a piggyback another patch all the
different queries into a single one so
this thing can't with our changing
filters can be a very expensive
expression depending on how large your
model is in this presentation of course
we don't have time to with men is for
query to come back but as long as you
understand that this particular pattern
and data you're calculating if you are
if you find yourself calculating a
distinct can't wear with a changing
filter a filter that actually changes
from row to row inside your report then
depending on the full size of your data
it can be potentially a very expensive
calculation let's go to the next slide
okay so let's look at
other common query pattern that that can
cause some side effects that other throw
people off index so one of the powerful
feature of taxes you can define measure
many many different measures and that
they build on top of each other right so
here is a very common example some
people say I want to define measure just
to get the current date okay so so there
are different ways to do it under the
way this particular said okay I want to
define a measure by the way this conduct
date is a measure in this example and I
want to say I want to get the the
currently selected date
some people use and mean some people use
max and so in this example we use a max
over the date column together the
currently selected at date under then we
know that there are different ways of
doing time intelligence functions we can
here I'm showing you a manual way even
though many people just use total y2d
function to do the same thing the reason
some people do the manual way is because
they want to put in some other filters
sometimes that they don't want it they
want that their their y2d is not at the
calendar y2d but one of those retail
calendars that they have to do the y2d
themselves so the people for all kinds
of reasons and you to write their own
version of y2d so I'm just showing you
you can do it using the calculator
function to do your own y2d saying let's
have a measure caller told ourselves so
I want to say calculate the total sales
and then I want to filter the date
column and at this time we smartly used
all of day to remember that's a good
thing if you're not sure they're doing
you're just doing order date other than
they said if the the year of the date is
equal to the year of the current date
remember we have a measure that can give
you the current date so if I said the
data is equal to the current the year of
the current date and then the date is
less than or equal to the current date
so we're in the same year but I want to
get all the days from the beginning of
the year all the way to the current date
and then this will give me
the list of example from January to
today all the way to Delta today and the
day I use this as a filter to calculate
total sales that will give me the sales
from the beginning of the year all the
way to today alright so so this works so
let's see how it works in the actual
report go back to desktop so here I have
a report right already I did two columns
the Year column under the month column
and then I'm showing you the total sales
and now that's added the way to details
to this report so there are different
ways so first I want to show you I do
have the current date the current date
is simply using the max of the date
column to extracted the current date and
then I have defined the to y2d and let's
first see the first one the first one is
what I just showed you it's basically
saying I want to calculate the total
sales of filter all the date column and
then the the year of the day there has
to be the year of the current date and
then the day it has to be less than or
equal to the current date so I'm in the
same year from the beginning January 1st
all the way to the currently selected
date it should work let's see if I add
this measure to the report it doesn't
seem to work because it's a returning
exactly the same number all the way
across okay and why why do we have this
problem if I go back to the slide here
is the problem measures index
automatically pick up all the filters in
the surrounding context and so in this
particular example by using the measure
name directly here it's actually saying
I want to be filtered by
in the report plus whatever the
candidate is in this iteration function
since the filter function belong to a
group of function called the iteration
functions it basically takes all the
day's going them one data at the time
and then I check this predicate by the
way I'm evaluating all this big
predicate expression there's some
measure references in there and the each
measure will be filtered by the grantee
role in the iteration function so so
this kind of data is going to be picked
up by this state in addition to the and
whatever the outside of date is and that
and so that's not what you want actually
because why do you want this kind of
data is you want to be filtered by
whatever is on the report selected the
current date but you do you on the
ignored they stay they stay this I want
to produce at different days right so I
want to say for all the dates that's a
lesson where you go to the data selected
on the report so so this is a very
common thing many people put a measure
inside a iteration function so filter is
the integration function sum is the
iteration function summing some acts all
those functions are Eurasian functions
last time in if we go back to last time
we said that there are many sequel many
tax functions in surprises sequel and
that many of those the sequel functions
they produce iteration functions such as
add columns top and filter and all the
like assam acts in cannonade acts
average acts and so so many of this
psycho psycho style tax functions are
iteration functions iteration function
means that they have a they are scanning
over a table expression going row by row
and they evaluate something else and we
are you adding some cells you have some
a measure expression on there those
measures will be affected by the current
row in the current iteration that may or
may not be what you want
and in our current case this is
absolutely not what we want because our
current data what we really want is that
we want to pick up the kind of data in
my kind of report and save it but I
don't want that to be to be changing for
each law in my iteration so the way to
do it is you can use the tax variable so
I'm still using a manager candidate but
I first evaluated it before and heard
the iteration I just say okay I want to
get to the current data based on the
Condor reporter filters under than I
save it into a tax variable once you
save it it no longer changes and then
inside my iteration I'm still doing the
same thing I want to filter all of the
dates and then the the year of the day
it has to be year of the variable which
is the selected date and then the date
itself has been less than or equal to
the selected date because now the
selected date I'm using attacks
variables as evaluated before I enter
the iteration this measure no longer is
affected by my current the filter in the
iteration itself so let's see how it
works out we go back to the report and
I've created a measure called y2 T cells
- this time I'm using the variable to
pick up the date and save it and the
inside of the iteration I'm using the
variable instead of using a measure and
now if I added it to my report
that I can remove the first one which we
already know doesn't work and you can
see the report is actually works right
so December is the December and the
January is the same as the total sales
february is the sum of January and
February March is a sum from January
February March sensing for April for me
keep going up until December and then in
January of 2012 it go back to January
total sales so this expression works go
back to the slide so this is a another
common pattern you may want to pay
attention to that you should be very
careful when you use of measures inside
the iteration functions again sometimes
that's really what you want for example
you can define measure let's say total
sales and then you can put it the
insider average X function average X you
can go over a list of something and then
you do want to say for each row you want
to use the measure that's actually
filtered by the control in the iteration
in order to calculate the sales for that
particular says they average over years
for example say for each year you do
want to the measure total sales to be
filtered by this year and then you you
you you go over all the years and then
to the average of the final result so
sometimes you do want to put the matter
inside the iteration function you just
have to do it knowing what consciously
instead of England apparently using a
measure in Sun in the middle of
iteration function next so palang will
talk about the palang Blanc is a very
special thing in Microsoft V is always
been very special ok it's especially in
many different ways so I'm gonna just
talk about the three important aspect of
it first it was first why is all the tax
filters all the tax visuals has
implicit a filter you don't have to do
anything it's implied data that is all
the power bi reports and all the Excel
pivot table reports by default will you
can drag some columns to way that you
can drive you can set some filters you
can drag some measures and by default
that they will have an implicit a filter
we call it a non-empty filter that means
I want to remove any rows where all the
measure values are black so that's a
that's a implicit and non-empty filter
and the second is in index we will add
something called a blank row to
dimension tables if you have a
referential integrity violation
what is the ri violation our evaluation
basically is for example in many
corporations the dimension tables they
belong to the they call the master data
right there manage the separately from
the transactional table which are the
fact tables so the fact tables can have
the this in some corporations it takes a
while for let's say you have a new
products takes while for them to enter
product catalog to be added to the
product table while the any sales of
this product they need to be immediately
reflected in the transaction fact table
so the fact table can have a more
products that have not been even added
into the product table yet in those
cases we took the macro saw bi the how
do you drawing between the fact table
under the product table and there is
some other bi products they want to do
it and say I want to do a unit ROI where
I want to do i laughter outer join right
I want to keep if I want to keep track
of all the sales regardless whether the
product has already been added to the
product table then you have to do it
after auditory and but if you only want
to show reports that actually have a
real product name and the product other
attributes that you want to do a unit or
power bi is very powerful in this regard
it gives you the flexibility to do
whatever you want you can do inner join
working to left outer join you don't
have to predefined the joy many other
products force you to pre-select or joy
so if you want to do the other
you have to you pretty much have to
build a different model that's too much
probably allows you to build a single
model and then even even even at a query
time you can switch between which type
of drawing or what but but how do we do
that is because in power bi you are not
draining the table beforehand right your
you are importing the table in the
individually so each table is all by
yourself you have inquiries the table or
by yourself you without requiring the
drawing at all that gives the power bi
super flexible so the way we do this is
that we if there we will we import the
data because then we know when we build
it the relationship between the
transaction they go under the dimension
table then we know that all if there's a
mismatch between the two do I have some
transactions in a transaction table that
don't have the corresponding as a
product key in the product table you
know if that happens we will add a blank
row to the product table so so when we
materialize all the relationships will
also detect whether there is our
violation across the relationship and
the window we will automatically add a
blank role to the one side of the
relationship this way in the report you
will see some people say annoying me you
will see a blank of value that actually
is an indication of something a real in
your date that means here at data
there's a mismatch in between the two
sides that may be intentional or may be
unintentional you as a hint if I were to
find out what's going on there and the
third thing about the blanket is it's a
it's a how we deal with individual
blanket values let me go back to this
later on I want to show the example the
first two cases so we go to a desktop
okay
so
so in this case I have a if you if you
look at my schema
oh it's there's a lot of other typos in
there soon the schema so if you cannot
see everything and you will see that the
in the product table I will have zoom
out zoom key for my view so you will see
that maybe it's just easier to see it
here so for my product table it's
actually connecting it to the product
subcategory table and the product
category alright so in this particular
example I instead of having a single
part of the table that has the category
column and the subcategory column I
actually have for the product table
connect to the product subcategory table
and the sub product subcategory table
connect with the product category table
so even though none of them is a truly
transaction is a fact table but they are
I think they have a many to one
relationship right there are many
products in a single product subcategory
and there are many product subcategories
in a single particle category so the
same principle of our evaluation up
still apply here you may have some
products which their product category
has not been added yet to the product
category table which actually is the
case here
so I want to say for for each product
category how many products are there so
you can just add a department name
directly to the report and the instead I
was showing the individual names I want
to just say Condor products let's do a
simple cut
so you'll say in this case I say for the
four categories I get their account 35
or 125 48 and 189 but I have another con
which is 209 that doesn't have a product
category name why is that is it exactly
as I said we have some new products
being added to the product name but in
this particular day the database the
corresponding product category has not
been added yet but we don't want to lose
that right so the actual product table
do have a 606 products in there so I
don't want to lose the count of them
that would be very confusing to the user
so power bi is very smart it
automatically added this blank row to
you to say okay and we have a two
hundred and nine products that don't
have a corresponding product category so
this is a the example of a blank row
example so this blank row we have
automatically added to the product
category in this case it's on the one
side right parlor category is a
dimension table of the product a really
indirect dimension table and let's look
at the second report in the second
report I'm adding for each partner
category I want to add the total sales
the first thing you say is that on the
left there are four product categories
there are actually five for other
categories why is a blank one under then
there are four named one accessories
bikes clothing and the components on the
right why are the total sales to this
report first of all you notice that
there are only three rows this is
because that non empty field so as it
turns out we don't have any transactions
for the components
product category so by default we don't
show it even though it actually exists
in the underlying table similarly we
don't have any sales for those of
unnamed product categories so for those
products which the product category has
now been added yet they also happen to
be no sales
in the sales table so we don't show
those to product categories because by
default that 90 filter will remove that
of course if you want to show them you
can use it a nice feature we call the
show items with no data so in which case
the other product categories will be
shown to the report and that they have
no sales by the way MPI as I said no
sales is now the zero sales but blank
cells the 90 filter are me remove
phalanx they don't remove zeros but for
some people I have seen not in this
particular report but for many reports
people don't want to show blanks in
their report unfortunately currently
there's no UI feature for you to say I
want to show blank as zeros so many
people use the tags to achieve that so
there are multiple ways to do it here
I'm showing you one way up so here is
one way to do it yes you do a sum of
sales amount plus 0 so plus 0 means of
ways of blank blank plus 0 equals 0 and
that 0 is not black by the way so many
people use this trick to show - in order
to convert a plan to zeros but it has a
side effect in this particular case if I
add this measure to my second report you
will see that the actually let me first
yeah you will see that you will see all
5 rows now you no longer the the party I
no longer remove components under the
blank of product category because as I
said the nandi filter doesn't apply to 0
it are may apply to blank values in this
small report it may be ok it's not too
bad but if you have a really larger
report especially if I have a me
if you he's over at the corner concrete
level you show it at the private level
then we're gonna produce wino per
product even though maybe only a small
percentage of the products only have
cells then you will still say all of
them so now that is actually more
importantly it can be resource intensive
because a power bi need to actually
produce such a large result before it's
sent back to the report
it can be very memory is a bigger memory
hock and if you add a cup of columns
then there's going to be a cross running
for example if you add a product and the
customer to the report then you're gonna
get all the combinations our product and
customers and most of them will return
zero instead of a plank which will be
automatically filtered out so some
people run out of memory simply because
they were doing this trick in power bi
using the plaster go back to the slide
okay so what what so the the lesson we
learned here is a this converting plank
to their thing
really should be a UI feature right that
is fundamentally a calculation and
analytical language it's used for you to
produce calculation results this is
really not supposed to be used for the
UI purpose
so you guys if you you shouldn't really
mode for a feature an idea in ideas
party I come to say okay please add more
formatting capability in the UI on the
UI side so to display blanks as a zeros
or any other sensor and a whatever you
basically a user supply the display
value and by the the underlying data
query should return exactly the same
thing instead of a returning different
values on the tax side which will
actually change the number of rows
sometimes that will be returned to the
to the video that there has many
unwanted side effects
and the last thing about the blank is
blank index is treated as equal to zero
the false that makes sense because in is
four people come from the sequel
background in single many people single
blank as a note right
but the blanks actually is not exactly
the same as notes
now since equal means unknown so you
have a cell if you have a mount of
ourselves and now that actually means
that I don't know the amount of cells it
doesn't mean that there's a zero cells
but in P I we decided to choose a blank
to mean zero because when you have a
combination of a product and the
customer you have a blank cells most
people means that I have a zero sales so
so I can do comparisons and I say I want
to do a filter anything is a lesson I
got 100 that I actually should include
that this guy instead of showing me
unknown for this particle for this
combination of product and the customer
I don't know the cells that's not where
the actually the means but unfortunately
while it makes sense to treat a blank as
equal to zero some measures we also
extend it under some like a numerical
columns we also extended this basically
universally to all equivalents are
blanks from the programming language
perspective therefore blank is a compare
equal to 0 and false or even empty
string or the origin of date all these
kind of things in my opinion some of the
design is questionable and for the
categorical columns are blank shouldn't
really be treated as a no instead what
we did as a equal to the origin that's
not what the user has intended but it is
what it is
so if you do if you use the equal
compare comparison operation many of
things will return to for example if
you're right if black equals 0 it will
return true but of course people don't
write this people say if column equals
blank or 0 some people are surprised by
the fact that both blank and 0 are
actually
- and similarly if Bronk equals false
it's also true Blanc equals empty string
also true and and you can use East Blanc
which to detect that whether or not is a
true Blanc or not
yes Blanca on a reference to when it's a
true black it's not it's going to return
false
for zero false or empty string and and
that the dachshund operator is a strict
operator in the sense that it was not a
match blank to zero it will only match
blank to actual plant and at zero to
actual zero similarly that that's a
switch function is also using strict
comparison it's army just at the equal
operator and it's a similarly the
greater than or equal less than or equal
or less than greater than all those
Annika the basic comparison operators
they were using this equivalent value
comparison logic while some tax
functions like a East blank or the yen
operator was a switch function they
happen to use a strict comparison for
the blank values this is a corner case
but you may want to know this just in
case you run into something you have to
scratch your head for a few hours and
not and don't understand what's going on
there so we are at the exactly one hour
mark I just have one more topic then
we'll we'll be done so it's a good thing
we have a reserved the tours so we have
a little bit of extra room to cover one
last topic this is the time Intelligence
functions Prime Intelligence functions
is one of the most powerful tax
functions and the most widely use is
also the most confusing functions
indexed so if the first thing we should
know that just like we said the last
time with that attacks functions come
from three major categories for me excel
and the pharmacy quo and from MDX the
time Intelligence functions belong to
the MDX category what it means it means
they manipulate filters all the MDX
is the distinct feature of MDX is it can
define measures that you can now do in
Excel or in sequel and that the measures
is picking up the surrounding filters
and the time Intelligence functions
actually filter manipulations
well Excel also have date functions for
example they have a day date Yeomans
under the Excel functions are used to
manipulate individual date values so
some first are some basic knowledge the
first one is the duck store all the days
as a sequential number starting from the
origin of date that is December 30th
1899 why do we pick a such a weird
starting date
we are following actually Excel
tradition because remember we directly
took a thousand library code and this is
a even though you sell the official
document the tenancy so but the is
actually because of some historical
reasons December 30th 1899 is the origin
of Excel data therefore is the origin of
tax states so many of the excel
functions like a date and time function
and the internally Excel is also
representing this as a sequence number
from the origin so tax have the same
tradition where's the whole number whole
number part of the sequence number being
the total number of days starting from
the origin under the fraction part is
the fraction of a day and most of the
Excel functions can be used to extracted
let's say that you can extract a year
month or quarter from the date value you
can construct the new date values from
different from the parts I say I can say
date 1819 I comma 12 comma 13 so I use a
three different components to construct
a new date I can convert the tax values
to days I can shift an individual date
value okay
and produce a new date value and I can
do a whole bunch of things but all of
those Excel functions are operating on
individual date values while the timing
tightness functions they work on a date
column because they're manipulating
filters and the filters are index our
actual column of values and so also the
time in hundred functions cannot produce
new dates so you can say Oh after they'd
add function that's a really convenient
function for me to add for example a
year to the existing bed and then I
produces the days a year from now no it
doesn't work that way you can only
produce this if those days also already
exist inside your date table so for all
the time intelligent function it
actually is just shifting days from
certain set of a typically the kind of
set of dates to either a past or future
set of days and then you using the new
days as a filters to compare do to
calculate the measure results let's look
at a concrete example here I have a tax
expression what he says is I'm using the
add columns function to add a column and
the summarize function in this case you
can think about as a guru by so
summarized of the date table year and
the quarter is literally just give me
all the distinct combinations of year
and quarter in that date table so I'm
getting a list of a year and quarters
and then I'm using I add a new column to
this table of two columns and that this
new column is calculated sum of sales
amount I'm using one of the timing
timing functions same period the last
year and that you order you may not know
exactly how it works but for me is the
name you can tell says I'm trying to get
for the year and quarter I'm one trying
to get the sum of sales of a last year
same quarter right so that's that's a
that's what this sounds like is doing so
let's say how exactly do it first of all
is it's a I started from a table of two
columns a year and quarter in this
database I have
you have two years 2012 and 2013
therefore each year has four quarters so
have a the rows and the columns is one
of those iteration functions it's going
to know by role and then calculating
this new economy expression so I'm going
one row at a time and for example this
blue this highlighted row I happen to be
on this row ok 2013 second quarter let's
say I'm on this row and I need to
calculate this expression calculate sum
of sales amount same period the last
year so here's what happens first last
time we said that the calculated
function that's one thing is to convert
a low contacts into future contacts so
it move the Condor row 2013 2nd quarter
into a filter now I have a new filter ok
year quarter by the way the same here in
the last year is just going to produce a
list of dates and what's the list of
dates that produce it's going to produce
the same period which is the second
quarter last year which is going to be
2012 so what is the returns is instead
of a quarter and a year is actually all
the time in tonnage functions work at
date level it always produced a list of
dates so what it does is that returns
all the dates in the second quarter of
2012 that is from April 1st all the way
to June 30 years ok and also once I this
new filter is added to the filter
contacts the existing filter is removed
this is a special thing of timing
tangent function right I mean that
function will remove the filters
existing filter why if I don't remove it
then I will have conflicting filters
I'll have a filter of 2013 I will also
have a filter of a bunch of days in 2012
so you cannot possibly have the same
sales table that can survive both
filters so you can only apply one of
them and obviously we want to apply the
new filter so the new filter will
overwrite the old filters now I'm using
the new list of dates which is a April
1st all the way to June 30th 2012 to
calculate it ourselves
so each sale
roll will be used to match against that
to say Easter sale state happens to be
one of the days if so I keep that to
keep the row and all the surviving rows
will go through the some function to the
aggregation function to some of the
sales amount for the other particular
dates so this is how next time
Intelligence functions work they do
filter manipulations right so they take
existing filters related to date and
then produce a new filter which is
always a list of days typically to count
some type of shifting either into the
future or into the past and then the new
filters will override the existing
filters and used to calculate whatever
you want to calculate and the last but
not least I want to say that because the
time intelligence functions they
currently work at a whole year the year
quarter month and the individual state
level for example it doesn't work at a
week level and the word also it only
works at the regular calendar so you
have a one with those like a four or
five and it's also like a retail
calendars you have to do your own timing
tenders functions using the calculate
function like a we have a shown before
time tending functions is effectively a
complete function by setting the correct
date filters and because our building
timing timing functions work at a year
quarter month and data level and we they
have to make sure that they do data
shifting they better shift them as a
whole also remember they also shift at
individual days they don't shift a month
they actually shave all that days in a
month so sometimes you see example
saying I could not do certain things
that's because you're not shifting full
like a month or a full year worth of
dates let's say Y example this used to
be a much more prevalent problem but
there since then we have these some
improvement in to tax so this error this
confusing error is much less likely to
happen nowadays this will be our last
time of the day
let's go - actually this not be added
one more demo since I have two hours so
I decided to show one more thing okay so
so in this report I have added a line
chart in this line chart of the a bunch
of a month I did a bunch of a year
months so I'm showing total total sales
over year month okay and I also happen
to have two slicers one slicers allow me
to select two years 2012 and 2013 which
have data I don't have love the date in
other years and I also have another
slicer showing me the weekdays so being
as powerful as it is in tax I can
actually select the individual days I
want to see all the cells inside on
Saturday mr. likes Saturday okay finally
so now I'm showing you the star just
know the Sunday to Sunday sales over
different months in 2012 and the 2013 I
can also show if it will let me click on
it a power bi respond
something that my click is not okay so
that's a Saturday cells and Sundays
there's why is this so slow okay the UI
is taking its time to think I don't know
why we're not seeing any of the tax
queries they're not coming in okay
they're coming yeah the UI test okay so
for example I have selected a Saturday
and a Sunday and the slicer and this
reported nicely respond to my filter new
filters is showing me the monthly sales
over the two years by the army account
for the weekend the sales really nice
and that's because my manager happened
to be a basic manager is a sum of sales
amount so let me clear the filters and
now if I add a time inheritance function
so the time inherent function is I'm
using same period last year
if and I'm calculating total sales the
same purely the last year so if I now
add this to my report and you can see
the black line is the last year so sale
you can see it's a is the black line
here in the 2013 is very similar to the
2012 right have the same shape of 2012
that's what is supposed to me and
because I'm going to the last year sales
I'm compare year-over-year cells in the
same period but for this report if I try
to do the same thing even the only one
to do the weekend sales analysis
you
what's the UI thinking
yeah there's no query here we're not
running anything the UI is a spinning
here for some reason okay so finally you
can see now I got an error if I look at
the error message it says the same
period the last year this function
expects a continuous selection when the
data can't come from different it's
because by selecting Saturday here I'm
no luck I'm breaking up I'm introducing
gaps in the selected days right I'm
selecting every I'm selecting all the
Saturdays and and in the selected period
by the way I'm trying to shifting the
whole year I'm not getting the full year
over stays under the tax engine when
they do shifting because it were always
working the whole month or whole year
depending on what the function does it
depend on time intended function so in
this particular case is trying to shift
in the whole year but it says I don't
have the 365 days so I can do it so it
raises error and so this society I'm
only showing this because as some people
still run into this particular error
they get confused but they this
particular error this continuous day the
selection error only happens to time in
turbulent functions due to the nature
how the time you can use functions work
last but not least wasted let's go back
to this so now that we know time
internet functions are always working on
a calendar table that the reason how you
need a calendar table is that you're
manipulating filters and that the
filters better exist you shift the days
to the past or to the future but those
days must exist inside your calendar
table otherwise it doesn't work right
and also in the current hyper better
have the continuous list of days some
people like to use the for example the
cells they'd come directly from their
transaction table I did sometimes it
works sometimes it doesn't
it's for the same reason as I said it
has to work on dates that actually works
so some people are seeing customer
complain that how come my report doesn't
work and the way I go to the previous
month
I'm comparing that the current the
current month which is literally March
2019 it it turns out that they are using
the date
directly from their transaction table
obviously the army have the transaction
up to the current day in March they
don't have any future days and then when
the DAX function try to shift in and
over it's now that they're shifting at
the data level but they have to collect
all the days in a month so they will
find out that they don't have a partial
month so when they go back to for
example February to try to compare to
the previous month they would army but
it found that if you have all the days
in March because you don't have a like a
31-day so you aren't having a good I say
19 days but the tax engine doesn't know
that it's odd oh these are all the days
I have in the underlying calendar so I
want to compare to the entire month of
fabric which is absolutely not what you
want you want to compare to the same
period that the same number of days in
February some depending on your business
logic but in pending any case what I'm
trying to say is if you don't use the
date column from the calendar table
which is a dedicated a full fledge in a
no gap they'd calendar table you are
using the date come directly from your
transaction table you may run into nasty
surprises so to avoid that and also to
have to give people a nicer
default the date hierarchy the power bi
automatically produce calendar table for
you by default but how come you don't
see it because if they are hidden from
you and this will be truly our last them
so let me open my new power bi desktop
so let me just create I want to show you
to create a new model from scratch and
then you will see that I don't introduce
any commenter table by the somehow they
show up by themselves
okay so let me create a new model from
scratch
I select the adventure works database I
select that I say fact the internet
sales table and for you restoration
papers so you can see in fact the
internet sales table I have some data
column called I have several date
columns actually I have a water date
do they initiate it over three days
column and let's say I also happen to
add the sum product table
let me either the product able to it
under the customer table to it notice
that I deliberately ignored the date
column the data table of no now the
employee table by the customer table so
if I know that this three tables into my
report how import data
so you can see I have the three tables
in my report and I I deliberately
skipped at the date table but I think I
seem to be able to do analysis
immediately so I can produce a report a
line chart and the date tape the data
table oh and I say order date directly
to my report and I can do I say I want
to say the total sales over time say
this is total sales over a year I have a
nice hierarchy I can go to the quarter
level I know I wanted a year and quarter
I can go to quarter year quarter I can
go to month month level right so I
already have a date
automatic a dead hierarchy and and I can
to announce a sky also do time
intelligence I seem to be able to do too
let me add a new matter
I'll call the white hoodie sales and I
would say using that a stamina meter so
total A to D and the sum of having to
fast for intelligence analysis to kick
in some sales amount sum of sales amount
and I don't have a date column by the
eyes I remember I was using order date
order date and you can say first of all
you notice out that it has the taxi
inheritance automatically show me this a
dot and notation so if I see enter it'll
actually automatically append a dot data
to it I don't know what it is but I
added anyway because the analysis
automatically added for me for some
reason another hour added the the work
that the new measure to my report
where's my new measure where do I had it
and we fund where am i y 2d is here and
to my report and yeah it just looks like
a y2d sara is it gross it's basically
drop it at the turn of every year and at
the end of coral steadily throughout of
the year so it is a nice way to deep
measure right how does it work I don't
have a calendar table and did I lie
about it the the fact that you need to
bring are always add a table otherwise
time inherence function doesn't work no
that's not true it's because a tax and
you nice now the tax engine is the power
bi UI client automatically added a
hidden calendar table for each date
column inside your model alright so if I
save this model I call it demo out of
date I create a file called at them Auto
date I save it
I closed the file I opened this up again
this time I'm using a trick I want no no
that's the wrong one that's the wrong
command-line argument this is the right
one so the let me close this one you
don't save so the the first launch will
raise the error because I used the wrong
command line for different purpose so
but the second launch will be successful
so after two one the first one will
raise an error
say it says kannada load the model
that's a test because I didn't launch it
at rackety let me close it the machine
is very slow the second wash would work
but if I can close the first one all
right the second one let me open the
data file we just saved that yet so far
just Emily's demo Auto dead
this time since I said flag to turn on
to show all the tables that are actually
in the model instead of so you can see
in addition to the three tables we saw
last time the fact internet sales team
product and a team customer I have a
whole bunch of a local date data all
there's a local data tables is because
there are multiple data columns in this
model and by default party I will
automatically create a day table for
each of your date columns and that's why
also you have the nice automatic data
hierarchy and all the time inherent
functions can start working but you have
to remember to use the tax dot syntax to
make it work and some people say no
please don't do it for me I want to do
it myself how I can bring my own data
table I know what I'm doing then in that
case if you're carrying it off you can
just go to options and go to data load
and the turn off uncheck the auto
date/time under time intelligence when
you say ok you can see my time
intelligence measure stop working and if
I remove it it will go back to work but
it no longer gave me the date hierarchy
a Tamiya individual dates and also you
can see I only have a three tables now
all the local date table have been
removed
now it's time for you to bring your own
data table and connect them to your
transaction table and then perform the
tiny intentions against your own data
count your own calendar tables instead
of we're relying on RBI's the internal
one which is pretty depending on how
many data columns you have under the
render update it can actually either
quite a few more tables to your model
and the dramatically increase the size
so some the mega I remember some people
when they comply compare tableau to car
VI many years ago they say the Westham
below model so bloated
is actually turns out that he has some
date condom that has a using some
special day the two Markey married dates
so the internal day table having to
produce a really large internal hidden
day table and also he happens to have a
hundreds of date condoms in his report
and the devil will create hundreds of
hidden day tables and that but if I
simply turn it off actually the power of
the eyes compression can be very
impressive and can produce a much
smaller file so so this is to let you
know that to facilitate the out of the
box time internet functions and date
hierarchy we have this a feature that
automatically create are carrying the
tables for you if that's not what you
want just to turn it off so that'll be
yeah we have used about an hour and a
half so this will be the end of my
presentation
Thank You Jeffrey going over quick
questions before we end the webinar we
had one going back to your slide about
the blank and his blanks if you can see
can you see the questions thing and your
webinar and I mean I'm assigned one to
you let me take a look where and there
the questions show questions why is the
screen so small how do I make it look
bigger okay
in my experience it doesn't return true
by the wonder what doesn't return true
is blink related RI air
can you address is Branca related RI
error with us was what does it mean I'm
not sure exactly what this question is
referring to in my experience it doesn't
return to by the window ri reference is
materializing economy sprang or returns
true when referencing that column what
does that mean relational integrity
yeah but I'm trying to see what this
question is asking so one of the ways
for people to detect let me see let me
see maybe
okay so let me show you my report
let me first across this one this is no
longer what I want I guess I said if you
reference a related column that returns
plane due to knit and there's an RI air
yeah I remember we had a bug but I
thought that box were fixed a long time
ago yeah we used to have a bug that if
you do is blank on top of related
there's a body in there that is not
returning the correct result but I
thought that that was fixed if it's a
still a bug then you may want to send
send it to me directly I can investigate
so yeah we used to have some bug in the
East flank of a related function but I
remember that was a fixed a long time
ago I don't know if or this question is
about that bug or not
um yeah they said they'll retest it I
guess I have yeah all right well I guess
we finish that up thanks again
Jeffrey for doing the second part of the
Dex thank you everyone for attending I'm
like boy yep
like go is we were recording the session
and Jeffrey if you don't mind maybe
sharing up the they may ask for the
PowerPoint slides charm I'll give it to
it we had that didn't give it to me and
I can send it out to our group okay
and then everyone that attended and who
missed it I'll try to get the webinar
uploaded on our YouTube channel if you
missed the first part of it last week
but two weeks ago that is on there as
well so I'll send you guys everyone an
email probably the next couple days
well thank you again Jeffrey thanks have
a good day Barbara