cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Phogon Frequent Visitor
Frequent Visitor

Generating a full list of Project IDs using 4 categories (Using Crossjoin?)

Hi all, 

 

I have one table, which includes transactional level information.

 

The Columns are: 

1) Customer Name (i.e. Client A, B)

2) Category (i.e. Dog, Cat)

3) Project Name (Alpha, Beta)

4) Location (i.e. China, UK)

5) Many other useless columns relating to other categories)

6) Financial Amount

7) A merged unique ID from Column (1, 2, 3, 4) (i.e. Client A-Dog-Alpha-UK)

 

I want to generate a table which would generate a list of all possible combinations of the first 4 columns so the following table would have columns like:

Client A | Dog | Alpha | China

Client B | Dog | Alpha | China

Client A | Cat | Alpha | China

Client B | Cat | Alpha | China

Client A | Dog | Beta | China

Client B | Dog | Beta | China

Client A | Cat | Beta | China

Client B | Cat | Beta | China

 

and so on. 

 

I was advised to use the Crossjoin function. Any advice?

 

2 REPLIES 2
Phogon Frequent Visitor
Frequent Visitor

Re: Generating a full list of Project IDs using 4 categories (Using Crossjoin?)

Sorry just something to add on. 

 

There is no need for ALL POSSIBLE combinations. just combinations that exist in the transactional data (i.e. If there is no financials from Client A for Dog, then I wouldn't need that combination in my new table

Phogon Frequent Visitor
Frequent Visitor

Re: Generating a full list of Project IDs using 4 categories (Using Crossjoin?)

I have succeeded in generating a table as follows:

 

Table2 = CROSSJOIN(VALUES(Table1[Client]),values(Table1[Project ID]),values(Table1[Region]))
 
However, it creates a full list, even for combinations that don't exist in the initial data. Is there a way to filter out combinations that don't exist (i.e. If "Client A" does not have a Project ID of "Beta", then the following doesnt show:
Client A | Dog | Beta | China
Client A | Cat | Beta | UK etc.