Create new Table based on two other tables

[TiRohnCH]

Hi there

I have two tables in my model, one with a list of distinct accounts, the other with a huge list of entries for hours per user on several more accounts.

Table 1:

Account Key	Description	Other	Info
1	Description 1	Other 1	Info 1
2	Description 2	Other 2	Info 2

Table 2:

Account Key	Date	User	hours
1	1.1.2023	user1	2
1	2.1.2023	user2	3
5	1.1.2023	user1	3
6	3.1.2023	user5	7

I would now like to create a new table with only the Accounts in Table 1 from Table 2, which would look like this...

Table 3:

Account Key	Date	Description	User	Hours
1	1.2.2023	Description 1	user1	2
1	2.1.2023	Description 1	user2	3

How can I create this table? I tried CALCULATETABLE with filters but don't get it solved...

Thanks and regards

Martin

[johnyip · Accepted solution]

Hi @TiRohnCH , to build your desired table, further to the DAX provided by @johnt75 ,you can use the following DAX.

Table = 
VAR StagingTable = SUMMARIZE ('Table 2',
                              'Table 1'[Account key],
                              'Table 2'[Date],
                              'Table 1'[Description],
                              'Table 2'[User],
                              'Table 2'[Hours]
)
VAR Result = FILTER(StagingTable,[Account Key]<>BLANK())
RETURN
Result

[johnt75]

If you have a one-to-many relationship from Table 1 to Table 2 you could use

Table 3 =
SUMMARIZE (
    'Table 2',
    'Table 1'[Account key],
    'Table 2'[Date],
    'Table 1'[Description],
    'Table 2'[User],
    'Table 2'[Hours]
)

[TiRohnCH]

Excellent, thank you very much, that's how it works, the second solution without the blanks is the one I'll be using...

Kind regards

Martin