This tutorial will demonstrate methods using Excel’s Power Pivot Data Model, but the same applies when working with Power BI or most any other Business Intelligence or database application.
Below is a snapshot of a data model that has four tables: “Sales” which is our Fact Table and three Dimension Tables called “dCustomer”, “dProduct”, and “Calendar”.
The connections between the tables are as follows:
- [CustNum] connects “dCustomer” and “Sales”
- [ProductNum] connects “dProduct” and “Sales”
- [Date] connects “Calendar” and “Sales”
Here is the million-dollar question:
“How many different cities are we selling to each month?”
We need to create a distinct list of customer cities by month.
Something that looks like the following.
Featured Course
Master Excel Power Pivot & DAX (Beginner to Pro)
Think About This Process
When we filter the “Calendar” table by a month (ex: “Jan-2021”), the filter flows to the “Sales” table and filters all entries by that month.
This process would repeat for each month-year in the “Calendar” table. The “Sales” table would render a new result for each filter passed from the “Calendar” table.
Filters flow from the “one” side of the relationship to the “many” side.
Continuing with the “Jan-2021” example, if we filtered the “Sales” table to only display transactions from “Jan-2021”, we would be presented a list of customer numbers ([CustNum]) for that month.
What we need to know is the city that each customer belongs to. This would need to be determined by taking each [CustNum] entry and filtering the “dCustomer” table.
If every customer hailed from the same city, the answer for “Jan” would be 1. If they were all from separate cities, the answer would be 6.
How NOT to Build a Report
If you didn’t know any better, you’d think you could just build a Pivot Table by placing the [MMM-YYYY] field in the ROWS and the [City] field in the VALUES and then set the [City] field to use a Distinct Count aggregator.
This doesn’t exactly yield the results we were hoping for.
All we receive is a distinct count of cities from the “dCustomer” table regardless of any other factors.
The reason our results are all the same value is that there is no direct relationship between the “Calendar” table and the “dCustomer” table.
The Pivot Table even lets us know that a relationship may be missing to satisfy this report.
We need to include the “Sales” table in this conversation to help us filter the “dCustomer” table based on the [CustNum] field.
Featured Course
Power Excel Bundle
Writing a DAX Measure to Answer the Question
As we can’t just create a relationship between the “dCustomer” table and the “Calendar” table (there are no common fields between them), we will write a DAX formula that will traverse the three tables using the “Sales” table to coordinate the conversation. Let’s call this formula “Distinct Count of Sales City”.
The formula is below with an explanation to follow.
=CALCULATE(DISTINCTCOUNT(dCustomer[City] ), Sales)The CALCULATE function can alter our filter context.
The expression that CALCULATE will use is the DISTINCTCOUNT function. We will get a distinct count of the [City] field.
CALCULATE will use the filtered “Sales” table to further filter the “dCustomer” table.
Replacing the [City] filed with this newly created Measure, we see the following results:
Visualizing the Data Differently
To help us understand more clearly how we arrived at these numbers, below is a Pivot Table report that shows the month-year and city names aggregating the quantity by each.
Don’t focus on the “Sum of Quantity” as it is there merely to get the three tables connected. Rather, examine the list of unique cities for each month-year entry.
“What’s up with that total?”
You may have noticed in the earlier Pivot Table report that the total added up to 11.
Clearly, 4+3+10 does NOT add to 11.
Just as the entry “Jan-2021” is used to filter the Data Model (this example results in 3 different cities), the “Total Cities” will filter the Data Model to show a unique list of ALL cities (that being 11).
Pivot Table totals do not aggregate the rows above them, rather they aggregate the entire Data Model.
In most cases, these arrive at the same result, but as you can see in this case, they do not.
Solving the Problem a Different Way
Another way to answer this question would be to create a calculated column in the “Sales” table that uses a DAX Measure to perform a lookup operation on the “dCustomer” table to bring back the associated [City] for each [CustNum]. This helper column could then have a distinct count operation run against it to get the desired results.
Our DAX formula in our calculated columns named “SalesCity” would appear as follows.
=RELATED(dCustomer[City] )
With this new [SalesCity] information in our “Sales” table, we can easily derive a distinct count of this new column in a Pivot Table.
If we add this new field to the earlier Pivot Table, we see the same results.
Where the first version is more complicated (not by much), it does not add any bulk to the Data Model as everything is calculated in real-time.
The second version is easier to understand and possible implement, the penalty comes with increased size in the Data Model and a decrease in report performance.
Using the CROSSFILTER Function
If you are up for a challenge, consider using the CROSSFILTER function to aid in your quest.
CROSSFILTER is a function that acts as a CALCULATE modifier.
This means it allows you to change the direction of a filter via a formula (i.e., through a Measure.)
For example, consider the below Measure named “Distinct Sales City (CrossFilter)”.
As before, we will use the CALCULATE function to grab the [City] field from the “dCustomer” table. This will be examined by the DISTINCTCOUNT function.
The filter, however, will utilize the CROSSFILTER function. CROSSFILTER needs to know the two fields (one from each table) that are used to connect the tables. For us, those are the “Sales[CustNum]” and “dCustomer[CustNum]” fields.
The filter direction is set to “Both” so we can send filters in both directions, not just from the “one” to the “many” side.
Adding this new Measure to our existing report shows the following:
Notice that the “Total Cities” for the latest Measure is different.
This anomaly was because we have a city in the “dCustomer” table that did not have a sale entry in the “Sales” table.
Passing the unfiltered “Sales” table to the “dCustomer” table effectively says, “don’t filter the destination table in any way”. Thus, we have a full list of customers to count whether or not they made a purchase and show up in the “Sales” table.
Practice Workbook
Feel free to Download the Workbook HERE.
Leila Gharani
I'm a 6x Microsoft MVP with over 15 years of experience implementing and professionals on Management Information Systems of different sizes and nature.
My background is Masters in Economics, Economist, Consultant, Oracle HFM Accounting Systems Expert, SAP BW Project Manager. My passion is teaching, experimenting and sharing. I am also addicted to learning and enjoy taking online courses on a variety of topics.
