Forum Discussion
Create a new table starting from existing data
Hi to all
I have 2 tables with this structures:
Table 1
Column A: Employee emails
Column B: Code of Primary Offices
For this table I can have for different emails the same Code of Primary Company
Example
Employee email A Code of Primary Offices A
Employee email B Code of Primary Offices A
Employee email C Code of Primary Offices B
Employee email D Code of Primary Offices B
Table 2
Column A: Code of Primary Office
Column B: Code of Secondary Office
For this table I can have for the same Code Of Primary Office different Code of Secondary Company
Example:
Code Of Primary Office A Code Of Secondary Office B
Code Of Primary Office A Code Of Secondary Office C
Code Of Primary Office A Code Of Secondary Office D
What I need to have and I ask your help is a Table with this structure:
Table New
Employee email A Code Of Primary Office A Code Of Secondary Office B Code Of Secondary Office C ......
Many thanks for your support
Hi, gcaserta
You can try the following methods.
Column:
Secondary Office Code 1 = CALCULATE ( MIN ( 'Table 2'[Secondary Office Code] ), FILTER ( 'Table 2', [Primary Office Code] = EARLIER ( 'Table 1'[Primary Office Code] ) ) )Secondary Office Code 2 = CALCULATE ( MIN ( 'Table 2'[Secondary Office Code] ), FILTER ( 'Table 2', [Primary Office Code] = EARLIER ( 'Table 1'[Primary Office Code] ) && [Secondary Office Code] <> EARLIER ( 'Table 1'[Secondary Office Code 1] ) ) )Secondary Office Code 3 = CALCULATE ( MIN ( 'Table 2'[Secondary Office Code] ), FILTER ( 'Table 2', [Primary Office Code] = EARLIER ( 'Table 1'[Primary Office Code] ) && [Secondary Office Code] <> EARLIER ( 'Table 1'[Secondary Office Code 1] ) && [Secondary Office Code] <> EARLIER ( 'Table 1'[Secondary Office Code 2] ) ) )Table Result 1:
New Table:
Table Result 2 = UNION ( SUMMARIZE ( 'Table 1', [Email], 'Table 1'[Primary Office Code] ), SUMMARIZE ( 'Table 1', [Email], 'Table 1'[Secondary Office Code 1] ), SUMMARIZE ( 'Table 1', [Email], 'Table 1'[Secondary Office Code 2] ), SUMMARIZE ( 'Table 1', [Email], 'Table 1'[Secondary Office Code 3] ) )Is this the result you expect?
Best Regards,
Community Support Team _Charlotte
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
5 Replies
- amitchandak
Super User
gcaserta ,
Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data. Hi Amitchandak
Below the sample. Honestly I didn't find how to send as attachment the files
Thanks
Table 1
Email Primary Office Code
[email protected] EAXXX [email protected] EAXXX [email protected] EBYYY [email protected] ECZZZ [email protected] ECZZZ Table 2
Primary Office Code Secondary Office Code EAXXX EAXX1 EAXXX EAXX2 EAXXX EAXX3 ECZZZ ECZZ1 ECZZZ ECZZ2 The result:
[email protected] EAXXX EAXX1 EAXX2 EAXX3 [email protected] EAXXX EAXX1 EAXX2 EAXX3 [email protected] EBYYY [email protected] ECZZZ ECZZ1 ECZZ2 ECZZ3 [email protected] ECZZZ ECZZ1 ECZZ2 ECZZ3 amitchandak Hi replied with tables but i'm not sure if you can see them.
v-zhangtiCommunity Support
Hi, gcaserta
You can try the following methods.
Column:
Secondary Office Code 1 = CALCULATE ( MIN ( 'Table 2'[Secondary Office Code] ), FILTER ( 'Table 2', [Primary Office Code] = EARLIER ( 'Table 1'[Primary Office Code] ) ) )Secondary Office Code 2 = CALCULATE ( MIN ( 'Table 2'[Secondary Office Code] ), FILTER ( 'Table 2', [Primary Office Code] = EARLIER ( 'Table 1'[Primary Office Code] ) && [Secondary Office Code] <> EARLIER ( 'Table 1'[Secondary Office Code 1] ) ) )Secondary Office Code 3 = CALCULATE ( MIN ( 'Table 2'[Secondary Office Code] ), FILTER ( 'Table 2', [Primary Office Code] = EARLIER ( 'Table 1'[Primary Office Code] ) && [Secondary Office Code] <> EARLIER ( 'Table 1'[Secondary Office Code 1] ) && [Secondary Office Code] <> EARLIER ( 'Table 1'[Secondary Office Code 2] ) ) )Table Result 1:
New Table:
Table Result 2 = UNION ( SUMMARIZE ( 'Table 1', [Email], 'Table 1'[Primary Office Code] ), SUMMARIZE ( 'Table 1', [Email], 'Table 1'[Secondary Office Code 1] ), SUMMARIZE ( 'Table 1', [Email], 'Table 1'[Secondary Office Code 2] ), SUMMARIZE ( 'Table 1', [Email], 'Table 1'[Secondary Office Code 3] ) )Is this the result you expect?
Best Regards,
Community Support Team _Charlotte
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
