In this video we're gonna talk about and solve a problem involving many to many relationships.
Do you guys like many to many relationships? Oh, yeah, let's go. Alright, so this question was asked by
Mahindra Bertram on our Talk Power BI
Mahindra's from New York and he brought it up in our Talk Power BI live session last time by the way guys make
Sure to subscribe
and click that Bell so you can join us the next time you go live to answer your Power BI questions and that happens every
Single Friday we hang out and have fun
And that's where we were hanging out with Mahindra. Now, I wasn't able to solve this in the session itself. And which is good
That's the highlight of Talk Power BI
you get to watch me fail and get egg on my face will flatten my face all of that stuff
Right, and that's the fun of it. You know, I like the process of
you know kind of getting stuck and
Then solving it right so so here we go. So, let me line up the problem with you. Here's what's going on here.
It's really simple. We have content and we have activity and we have content has different topics
So again, I'm gonna refer back to my talk power bi notes. So content could be blog videos white paper, etc
Right and it can imagine the one content each content is represented by a Content ID in the data set, by the way
you can get this file on by just going to
Learn power bi comm slash download you can get this file and work through the problem yourself or look at the solution
so the content that's the content a I
Was in my notes so content via blog video white paper and on and on right now each
Content represented by the Content ID can have one or more topics make sense
I just wrote a blog post recently about my trip to Banff National Park
It's beautiful and you can imagine that can have topics like Banff
travel vacation
Family on and on right?
so each content can have many content topics fair enough and each content each piece once I
Published that blog it's gonna have activity. It's gonna have views and of course depending on the content type the activity itself
Kind can be different
It's gonna mean recognized by this activity ID
But you know mountain videos can have views white papers can have downloads maybe videos gonna have both views and download, right?
So all of that structure is there and this is set up well for that regard
So let me show you what the problem was occurring. So again Mahindra
Demonstrated that in the talk bar we a call. So what we're doing is we're selecting one topic
So again, the topic is coming from this and I'm gonna move this table down over here. So the topic is coming from here
I will talk about relationship by directions a little bit
but we filter down to a specific topic and he showed these files and he said
Ah be the right answer in this one is 16 and he did have a measure which was you're turning 16
And I'm like awesome. Well, what are you here for? Like I mean, oh boy. No, you gotta solved
Well, not so fast because he showed B to B and here
This measure doesn't work
The right answer in this case is 20 so Union he has one measure which works in one case another measure it works in another
case and of course
you could do some kind of a mash-up or you say if if this then use this measure if not,
Then use this measure
But again what I'd like to do in these cases to understand why it's not working in and just focus on the model itself
So again, I set out to solve it. So let's go back and focus on the model and the model is
fairly clean, but I'm gonna
Make some tweaks for one the default for you should be
One-to-many relationship just like this one
So that's the default that I look for anything else is an exception and you better be able to explain that exception to me
So so this one and so again, it's it's it's good to start with the default and then change it
so what I'm going to do is I'm going to change both of these to single direction and single direction and
Then come back and try to solve it. So again, I'll change this one as well
All right. This would probably break things in the report
But so now if I go back and think about so notice the numbers are kind of wonky now
But now I want to think about well, I'm trying to filter it by topic and I want the activity to be filtered
So now I go back here. And I said, okay, so where's my filter?
Let's see if I can any go to drawing. So that's my filter, right? Oh
Boy, that's a pretty nice looking filters. Wouldn't you say hey hit the like button my friend if you liked my drawing filter
Just kidding. Now. You can still hit the like button
all right, so that's the filter and then we expect so filter water relationships relationships our
filter transmission wires
So we want this filter which is on this table to be boop-boop-boop-boop transmitted here and that we know transmitted down here
But filter relationships by default the filters flow downhill
So this part is is good my friend, you know, this one is downhill right? You notice how the stable is downhill from here
It's one it work. This one is uphill so it's not gonna work by default
so again
we need to understand that and then we're gonna say oh so I need the relationship to filter uphill so I'm gonna change this to
bi-directional
now there's another thing here, which is probably this one this table shouldn't be
Set up this way
Probably our topic hierarchy should be here
And this should map the Content ID with the topic ID, right?
So this is the proper way that I would say would be set up, but we'll work with this for now. All right, cool
So we have this set up
so now when I ask the question that minder was asking is that oh
I want to see activity by topic
Activity by topic and I'm like there isn't there's this shouldn't be anything special we need to do because the filter flows up here
Filter flows down here. So if I want to see activity, I'll just count the activity
So in my mind, that's that's the measure. So let's go ahead and define that measure
All right, so I'm just gonna click modeling new measure
And when the dialog box comes up any time soon. There we go. I'm just gonna say count
Activity equals
count grows activity doesn't get any simple than that and
now I have
activity by
topic type
So let's try to analyze it
I toyed created a new page and I'm just going to put count activity there and
Then here I'm gonna go here and I will add the different levels stop it level one
Topic level two and they're all going to be kind of drill downs and so forth
And of course, let me quickly make a matrix as well. I'll be right back. All right, there we go
so now I have it in the graph over here in a matrix over here and and and
Um convinced that this is right. This is really simple and yet you can drill down and
to whatever level and
Same thing here
you can go drill down and brand and go now we're looking at level two and the solute content marketing and I think this
The level three is disappear for some of them, but that's okay, right?
so as long as the data would be there it would it would show up but
Let's let's go back to Mahendra's original problem of this weird count where he was expecting something else
So again, I'm gonna ignore this last topics one. So let's go back here. I'm going to select this visual and
Bring in our count
activity count activity, you know not here always measure and
All right. So so again, this is giving the right answer twenty great
but then go back to the next scenario of agency and
it's
Shoot it's not given that given the right answer. It's showing fourteen and we had said one of the higher number sixteen
So then I dive in and investigate is like what's what's going on? What is the part? That's not working?
so I'm gonna copy that table and I want to just remove just pretty much keep the ID the content ID there and
See how these
measures are working and
And I see that the sixteen the right answer is
Expecting some of these activities to be counted twice now again, this is unusual. So what they're saying is that
if if there's there's one content one blog and
Two topics if it was read once I still want to count it twice, right?
So it was listening under two topics. So and you can see that here
So if I click on this, so again, they're expecting the right answer for this Content ID. We did with two now
There's only one activity right because there is only one activity ID on this state
So one person probably read it or something, but if it has two different topics at topic level to counted twice
So again kind of an unusual scenario, but that's why
Is so fun because it's not always textbook so there could be different ways to solve it
But the way I'm gonna solve it is this way. So basically I'm saying no
No, I don't care about topic level one if there are multiple topic level two is I want that to be counted each time
So what I'm gonna do is basically create a measure which calculates the same count activity
But does it by each topic level to?
Each topic level two so I'm gonna say count
activity
by
Topic level two and then go so this is gonna go topic by topic at the level two
So that does mean that I'm gonna use an iterator function
so basically
I know it's gonna be some kind of a to trade room function
And I think it's gonna be some so let's just start with that. So some X and what am I creating?
What work well, topic level oops or whatever topic level two are available to me. So I'm gonna say
values
topic left
to right so this is gonna give me the table that the sum X is gonna iterate over and then for each of these what
Do I want I just want the activity? Oh, it's counter activity
So I'm gonna use a new measure that I created that I just created. All right, so so that's it and
so let's put this make sure it's in the right spot and
We're gonna add it to
To our table here as well. Delete this guy
And in this table, I'm gonna add the count activity and I'm gonna add it in this lower table as well
But you can see that we're getting now getting the right answer 16 here. Let's just check the other scenario
B to be make sure that that's still working and that's just working 20 so great
so again folks to recap we we have a many-to-many scenario, which
Normally there wouldn't be any heavy lifting involved as long as it's set up this way
The only change that I would recommend is having a mapping table over here
For the topic levels, that would be moved up here and that's it
And normally you would just do count rows activity
But we had a slight wrinkle of the specific requirement of how this needed to be counted in our case
So hey if you have any questions join us for talk Barbie
I live I go live every Friday and that's a chance to hang out discuss ask questions hang out with other cool folks and
Once in a while watch me fail. Alright until next time borrow my friends