in this video we'll take a quick look at managing relationships
in power bi understanding how it works and where it might cause some issues in your data sets
in this example our goal is to look at sales by year and if we look at our fields here
theoretically we should just be able to say calendar year and order quantity
so by default this is actually summarizing this calendar year here which
is because it's set to a numeric value so let's change that
to don't summarize and what we can see here happening and this is a pretty clear sign
that something's up with the relationship is for every single year the order quantity is the same
and it's pretty clear that that's not accurate you know we would expect different values for
each year so that's a pretty clear sign that the relationships are off and we need to adjust them
there's two ways to look at the existing relationships you could either go
to this visual over here of relationships and you can see how things look or you can
go up here to manage relationships and see it as more of a table view what's happening here
is right now let's go back if we were to replace year instead with you know customer name
this works fine we can see this is breaking down by individual customer and that's because
customer has a relationship to sales it's got this customer key and they are connected you
see if you hover over it that's what's connecting same would go for product product key to product
here as well the problem is once we go to year if we switch out this to calendar year
again don't summarize that relationship doesn't exist so it doesn't understand
the model doesn't understand that this order key should relate to this date key and it's
just going to repeat it it doesn't understand how to break it down on a more granular level
so the way to fix that is either to do a drag and drop here which you could do the other option is
to replace it here and you could say new will go from the internet sales on the order date key
on to calendar date on the day key we'll keep this many to one in the single direction but there are
other options that are outside the scope of this video for the most part you'll want to stick to
many to one most of the time otherwise it gets a little bit more complicated in terms of how
the modeling and the visuals are going to react to any changes in filtering that you make
we'll set this to active by default and click ok and now right away we can see that this this
now broke down correctly the order quantity by year looks more accurate and it's not just a
repeating value the total summary is correct but the individual year values look better
and that's because now this exists it understands this relationship
we were to turn this as inactive let's say you make this one inactive here
now we're back to the same problem if we flip this to active
we're back to where we want and the same now we could actually break this down by something more
because now all three of these can be connected so we could do occupation and have this broken down
even more granularly because it understands this relationship for every single row you could have
multiple inactive relationships either they're defined here or they're defined in your cube
and the way that you would indicate how you want it to actually connect would be through dax so you
would have to use the use relationship function within your dax query as you're creating a measure
and then use that specific measure in here let's say you had two keys on sales and calendar date
you had order date key and then maybe there was a sales date key you could create two different
dax measures both would be inactive one would be based on sales date the other would have
the use relationship indicating order date and depending on which measure you picked
they would understand how it should relate those and break those down for you appropriately so
that's all i wanted to show for this video please feel free to like leave a comment or subscribe to
the channel if you want to see more videos like this and i'll see you at the next one thanks