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.