Forum Discussion
Create new Table based on two other tables
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
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
3 Replies
johnt75Super User
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] )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
- johnyip
Solution Sage
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
