Help with DAX creating new table and sum from multiple tables

[Anonymous]

Hi,

I'm struggling to create a table using DAX and getting what I think are simple totals. My goal is to get a table with 2 values: Customer_ID and Product_Count. Customer_ID is the unique ID for a customer. A customer can have 1 more accounts (free checking, savings, 12 month money market, etc.) and every type of account (270 total) belong to 1 of 5 products. Think of a product as the main category and the account is the sub-category.

Here are my tables:

Customer_Final:
-----------------
Customer_ID (PK)

Accounts_Final:
-----------------
Account_ID (PK)
AccountNumber
Customer_ID (FK)
Sub_Product_ID (FK)

Sub_Products_Final
----------------
Sub_Product_ID (PK)
Product_ID (FK)

Products_Final
----------------
Product_ID (PK)

No customer can have more than 5 for Product_Count as this is the max number of products.

My table is returing 270 for every Customer_ID. That's the total number of accounts types I have, so clearly I'm doing something wrong. 

Below is my code.  I'm assuming I need a join between Sub_Products_Final and Products_Final but can't get it to work.

Customer_Product_Count = SUMMARIZE(
Accounts_Final, Accounts_Final[Customer_ID],
"Product Count", COUNT(Sub_Products_Final[Sub_Product_ID]))

What am I doing wrong?

Thanks,
Mark

[Anonymous]

// Don't use SUMMARIZE for calculating
// anything. Use it only for getting
// distinct combinations of values.
// This function is flawed and cannot be
// fixed by Microsoft.

Customer_Product_Count = // it's a table, not a measure
ADDCOLUMNS(
    VALUES( Accounts_Final[Customer_ID] ),
    "Product Count",
    	CALCULATE(
    		DISTINCTCOUNT( Accounts_Final[Sub_Product_ID] )
    	)
)

Best

D

[Greg_Deckler · Accepted solution]

Maybe a measures total problem? This looks like a measure totals problem. Very common. See my post about it here: https://community.powerbi.com/t5/DAX-Commands-and-Tips/Dealing-with-Measure-Totals/td-p/63376

Also, this Quick Measure, Measure Totals, The Final Word should get you what you need:
https://community.powerbi.com/t5/Quick-Measures-Gallery/Measure-Totals-The-Final-Word/m-p/547907

Otherwise, Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490