Forum Discussion
Mapping the Data with multiple source
Hi All,
Good Day!
I've been trying to solve this issue for days. Need your help on how to resolve this problem.
I have 3 sources of table
SSD table
| Customer Grouping | Product Grouping |
| Restaurant A | SKU A |
| Restaurant B | SKU B |
| Restaurant C | SKU C |
| Restaurant D | SKU D |
| Restaurnt E | SKU E |
PSD table
| Customer Grouping | Product Grouping |
| Restaurant X | SKU A |
| Restaurant B | SKU B |
| Restaurant C | SKU C |
| Restaurant D | SKU D |
| Restaurant Z | SKU F |
As you can see SSD and PSD table have almost the same customers the difference is these two come from different source
Both PSD AND PSD have dates which is connected to my calendar table
Outlet Table (From excel file
| Customer Grouping | Outlet # |
| Restaurant X | 1 |
| Restaurant B | 2 |
| Restaurant C | 2 |
| Restaurant D | 2 |
| Restaurant Z | 1 |
| Restaurant A | 1 |
| 1 |
These are the table from different sources, what I need to do are the following
1. Get distinct count of Grouping customer of SSD and PSD for the past 6 months.
(I already have a DAX calculating the distinct count of Grouping customer SSD and PSD . After getting the Distinct count of grouping customer of ssd and psd for the past 6 months.)
2. I need to map it to Outlet Table Customer grouping column to get the sum of outlet #.
( This is the part that I couldnt solve would appreciate so much if you could help me.)
I have been thinking of trying another approach which is creating a new table to get the psd and ssd distinct data and use it to map to customer grouping. Instead of creating a DAX.
Hi Anonymous ,
You can create intermediate calculated table like DAX below, then create relationships with your three original tables with "Both" of Cross filter direction.
Distinct Customer Grouping= UNION(DISTINCT('SSD'[Customer Grouping]), DISTINCT('PSD'[Customer Grouping]),DISTINCT('Outlet'[Customer Grouping]))Best Regards,
Amy
Community Support Team _ Amy
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
6 Replies
The table route may be the way to go. Would still be DAX.
I am going to assume that you have the DAX for the distinct customers, you could do this:
Measure = VAR __Customers = [Distinct Customers] //this is your measure VAR __Table = ADDCOLUMNS( 'Outlet Table', "Final",IF([Customer Grouping] IN __Customers,[Outlet #],0) ) RETURN SUMX(__Table,[Final])Hi Greg_Deckler,
Thank you for replying. I tried the measure you suggested. its giving me this error.
__Customers is supposed to be the distinct list of customers that you said you had, not their count, so something like:
VAR __Customers = DISTINCT('Table'[Column])
This returns a table of values, which is what you want for the measure.
Hi Anonymous ,
You can create intermediate calculated table like DAX below, then create relationships with your three original tables with "Both" of Cross filter direction.
Distinct Customer Grouping= UNION(DISTINCT('SSD'[Customer Grouping]), DISTINCT('PSD'[Customer Grouping]),DISTINCT('Outlet'[Customer Grouping]))Best Regards,
Amy
Community Support Team _ Amy
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi Anonymous ,
Does that make sense? If so, kindly mark the proper reply as a solution to help others having the similar issue and close the case. If not, let me know and I'll try to help you further.
Best regards
Amy
