Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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!
Solved! Go to 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.
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?
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!
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.
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!
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
99 | |
82 | |
70 | |
60 |
User | Count |
---|---|
149 | |
114 | |
107 | |
89 | |
67 |