Summarising a field in a dimension table, by a field in a different dimension table.

[knotty]

Hi All, hoping for a bit of help with trying to summarise some data in a particular way please:

Imagine your typical/standard star schema: dim_Date, dim_Customers, dim_Salespeople, dim_Geography, fact_Sales.  If I want to summarise the no. customers who transacted a deal by Salesperson, I'd create a visual of a distinct count of fact_Sales.Customer grouped by dim_Salespeople.Salesperson.  Dead easy, really simple!

However, if I want to summarise just the total no. customers (who'd transacted a deal or not) by Salesperson, I think I need to perform a distinct count of dim_Customers.Customer grouped by dim_Salespeople.Salesperson.  This will give me a Portfolio of Accounts by Salesperson.  Few issues I'm having:

1. When setting cross filter direction to both (between dim_Customers, fact_Sales and dim_Salespeople), what I end up with is just a distinct count of customers who'd transacted a deal.

2. When setting cross filter direction to single (between dim_Customers, fact_Sales and dim_Salespeople), I end up with just the total no. customers in dim_Customers against each Salesperson (there's 10,000 customers in dim_Customers, this number just repeats for each Salesperson).

Am I missing something dead obvious?  I understand why the cross filter directions are giving the me the results above, and as a SQL analyst I know exactly what I'd do to get the figures I'm after, I suppose I'm struggling more with the concept of star schemas and trying to adhere to best practices.  The fact I'm trying to summarise a field in a dimension table doesn't feel right.  Would a snowflake schema be more appropriate here?

Apologies if this is dead basic, I'm quite new to "best practice" data modelling in PBI.

Thanks

[MAwwad]

One approach is to use a bridge table to connect the two dimension tables. This bridge table would contain the keys from both dimension tables and any other relevant fields. In your case, it might contain the Salesperson key and the Customer key. You can then use this bridge table in your visuals to summarize the number of customers by Salesperson.

Another approach is to use a DAX measure to calculate the distinct count of customers by Salesperson. You can use the RELATED function to traverse the relationships between the fact table and the dimension tables. Here's an example measure:

Total Customers = DISTINCTCOUNT( RELATEDTABLE(dim_Customers) [Customer] )

This measure calculates the distinct count of customers from the dim_Customers table, but filters it based on the Salesperson selected in the visual.