Mapping the Data with multiple source

[Anonymous]

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.

[v-xicai]

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

[v-xicai · Accepted solution]

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.

[Greg_Deckler]

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])

[Anonymous]

Hi @Greg_Deckler,

Thank you for replying. I tried the measure you suggested. its giving me this error.

[Greg_Deckler]

__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.

[Anonymous]

Hello @Greg_Deckler

My apologies, the name of the measure is wrong. This is the measure I created if it expands. As you can see the measure is trying to get values other than psd and ssd during the last 6 months with condition, but it does not allow me since it says that it is not a valid table