cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Phogon
Helper II
Helper II

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?

 

1 ACCEPTED SOLUTION
v-frfei-msft
Community Support
Community Support

Hi @Phogon ,

 

Kindly share your sample data to me if you don't have any Confidential Information. Please upload your files to One Drive and share the link here.

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the others find it more quickly.

View solution in original post

4 REPLIES 4
v-frfei-msft
Community Support
Community Support

Hi @Phogon ,

 

Kindly share your sample data to me if you don't have any Confidential Information. Please upload your files to One Drive and share the link here.

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the others find it more quickly.

Its okay. I worked out a workaround. I just concentated all the columns together into a new calculated column in the initial table, then used a new calcualted table with the values( function to create a list of unique IDs.

Phogon
Helper II
Helper II

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.
Phogon
Helper II
Helper II

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

Helpful resources

Announcements
November 2022 Update

Check it Out!

Click here to read more about the November 2022 updates!

Microsoft 365 Conference â__ December 6-8, 2022

Microsoft 365 Conference - 06-08 December

Join us in Las Vegas to experience community, incredible learning opportunities, and connections that will help grow skills, know-how, and more.

Power BI Dev Camp Session 27

Ted's Dev Camp

This session walks through creating a new Azure AD B2C tenant and configuring it with user flows and custom policies.