Okay so i'm going to demonstrate this in excel but the same concept applies to power bi as well let's go to the data model so we can take a look at the structure of our data.
Set i'm going to switch to diagram view okay so i have my sales table this is where i collect the transaction number the date the sales was made customer number product number quantity and sales.
Price on the master data side i have a customer master data the connection between these is the customer number in the customer master data i have more information about the customer like.
Their first name last name city state region and country then on the product side i have more information about the product the connection between the two is the product number and then i have a.
Of customer cities by month something that looks like this now let's think this through we have a one-to-many relationship here this means that any filters we have on the calendar.
Table is going to filter down our sales table so if i am only looking at a special month so let's say january my sales table is going to be filtered down to january but i only have information.
About customer number here and i don't want to get a distinct list for the different number of customers i have i want to get a distinct list for the different cities i sell to that.
Information is in the customer master data this means that i have to go from my filtered list here back to my customer master data i need to go the opposite way of the relationship how do.
I do that well my first attempt is to ignore the fact that i have these relationships in place and just go ahead and create my report where i have the month and year combination on the one.
Posts Related:
Side and distinct count of city and then
Let's see what we get let's go ahead and insert a pivot table from the data model i'll just insert it right here i want to get the month and year combination on.The rows and city in the values so when i grab city drop it in values i automatically get a count of city i want to get a distinct account so let's go to value field settings and change that to.
Distinct count immediately we see that we have some problems here problem number one relationships between tables may be needed that's true because there is no relationship between those two.
Tables there is a relationship between calendar and sales there's a relationship between customer master data and sales but there isn't a relationship between the calendar.
And the customer master data that's why we have problem number two where we have the same number in each of these fields here these filters aren't working all we get is a distinct count of city.
From our customer master data there is no information about how many different cities we sold to now this kind of makes sense because we haven't involved the sales table in our analysis we need.
Sales to define the filter how can we do that well we can do it through a measure so let's go ahead and create a new measure i'm going to keep that measure in the sales table and call this.
Distinct count of sales city the formula is going to use calculate because calculate is the function that can alter our filter context the expression is the distinct count of our.
City from the customer master data and we are going to alter this based on the filter and that filter is our entire sales table that's where it comes into play close bracket let's.
Check the formula
And let's add this to our model and now we get the correct distinct count of sales cities i'm going to kick out that implicit measure that we created before.And we get the correct values here so all we had to do was add our entire table as a filter now how does this work i'm going to attempt to explain it to you down here.
On a separate pivot table i'm just going to copy the original paste it here and let's start from the beginning so this is our starting point we have the month and year combination here in.
The rows when we get a distinct count of city directly from the customer master data i'm going to drag city down here we get a count of 12. this is basically what was happening before so i dropped.
City in here let's actually switch this and view it in tabular form so we can see it beside one another so notice the count of these basically the distinct count for city is 12 right for each of.
These there is no relationship between the two and that was the result that we got before now when we involved the sales table this got updated automatically so let's.
Involve the sales table here as well i'm going to go to sales and grab one of the fields here that can be aggregated so for example quantity i'm just going to drag that and drop it in the values so.
I'm only using this as a placeholder to show you what's happening in the background the moment one of these fields comes into play notice what happens to the other two they are.
Automatically filtered how many different cities do i have here four that's the number i see there i have 3 here that's the 3 i see there 10 and that's the 10 that i have for march in.
Total i have 11 different cities so if i kick out the month and year combination from here i end up with 11 different cities that's how the sales table is acting as.
A filter in the background you can use this trick whenever you want to go from the many side to the one side now there are different approaches to this so you could for example.
Go to your data model and add a new column to your sales table and bring over the city and then do a direct distinct count on the city here because it's already in the sales table.
So if i switch to data view jump to cells i could do that by using the related function right so i can take advantage of the relationship that is in place and grab the city from the.
Customer master data and then let's just call this sales city now i can go over to excel let's go to our first pivot table here.
And bring over the cell city by default here i have the count of cell city so let's just double click and change this to distinct count and we should see the same values here right so.
You can expand your data model but it's going to come at a cost if you have a lot of data another option you have if you want to solve this with a measure is to use the cross filter function so.
Cross filter is a calculate modifier it allows you to change the direction of the filter by a formula right so through a measure let me show you how it works let's go and add a new measure.
I'll call it distinct cell city cross filter we're going to start with the calculate function grab the distinct count of city from the customer master data now we are going to use the cross.
Filter function to change the filter direction here we have to specify the column names that are connecting our two tables so one of them is the customer number from the sales table and the.
Other one is the customer number from the master data table now i am going to change the filter direction to both so that we can go both ways this way we can also go from sales to customer now let's.
Close the bracket check the formula and add this now you are going to see one difference between this and the other ways that we solved this before and that's when it comes to the grand.
Total so for these it works fine because sales passes over the filter to the customer master data and these are filtered correctly but when we don't have a filter on the sales table we get.
The distinct list of cities from the customer master data and we have an additional city in the master data that we don't have in sales so here we don't get the correct value.
Okay so there are different ways you can solve this you just have to understand the implications of each of the different methods and based on the type of analysis that you want to make pick.
The one that best fits that okay so this was a bit of an advanced dive into the dax world so i hope you found it useful or at least interesting do give it a thumbs up if you did and if you'd like.
To go from basic to pro with data modeling powerpivot and dax make sure you check out my complete course on excelplus.com link to it is below the video many thanks for watching subscribe.
