How to create COUNT() measure based on multiple criteria?

[hyggins]

I have the following tables:

Customers – list of customers

Customer States – many to many relationships of customers and operating states

Customer Categories – many to many relationships of customers and operating categories

Customer Options – list of options available to customers by State and Category combination

I need a measure that will count options by customer id based on their states and categories

See sample output with matching IDs for reference

How do I create/design the relationships to facilitate such a measure?

What would the actual measure code look like?

Thank you!

[v-eachen-msft]

Hi @hyggins ,

I still don't understand your expected result.

For example, Customer 1 have 2 states : NY & CA, 1 category : A. So I get 101,106,109,110. It is different from your result which you just keep NY. So, do you have any other relationships not mentioned?

[hyggins]

Hello @v-eachen-msft ,

It seems you understand my data better than I do. My apologies for the incorrect results table. See below for update.

Count of Custom Options Results
Customer	Count	Option Ids
1	4	101, 106, 109, 110
2	6	102, 103, 106, 109, 112, 113
3	2	108, 111
4	7	101, 104, 105, 106, 107, 109, 110

Regarding additional relationships, I do have an OptionsDim and CategoriesDim but didn't think that factored in.

I'm also reposting the table at @Greg_Deckler 's request.

Customers

Customer Id

1

2

3

4

Customer States
Customer Id	Customer State Id
1	NY
1	CA
2	GA
2	KY
2	CA
3	TX
4	WI
4	NY
4	CA

Customer Categories
Customer Id	Customer Category Id
1	A
2	A
2	B
3	B
4	A
4	C

Customer Options
Option Id	State Id	Category Id
101	NY	A
102	CA	B
103	GA	A
104	NY	C
105	WI	A
106	CA	A
107	NY	C
108	TX	B
109	CA	A
110	NY	A
111	TX	B
112	GA	B
113	KY	A

Thank you!

[v-eachen-msft · Accepted solution]

Hi @hyggins ,

You could merge these tables with "Inner Join" to get a new table "Merge1".

Then use the following measures to get result.

Count =
CALCULATE (
    COUNT ( Merge1[Customer Category Id] ),
    ALLEXCEPT ( Merge1, Merge1[Customer Id] )
)

Option Ids =
CONCATENATEX ( Merge1, Merge1[Option Id], "," )

Here is the result.

Here is my test file for your reference.

[hyggins]

Hello @v-eachen-msft 

Thank you so much for your suggestions!

Your ideas lead me to the result I was looking for. I appreciate your time!

[Greg_Deckler]

Can you post the data as text so we do not have to type it all in by hande?

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