Forum Discussion
Solved
Create a table from filtered data from another tables
Hi all,
I have a few tables that I work with. I want to create a table from filtered data from different tables.
for example, I have these values in these tables:
account_move_line
| date | account_id | balance |
| 01/01/20 | 1 | 159 |
| 01/02/20 | 2 | 145 |
| 01/03/20 | 3 | 651 |
| 01/03/20 | 3 | 12 |
| 01/04/20 | 4 | 354 |
| 01/05/20 | 1 | 365 |
| 01/06/20 | 2 | 168 |
| 01/07/20 | 3 | 325 |
| 01/08/20 | 4 | 654 |
| 01/09/20 | 1 | 138 |
| 01/10/20 | 2 | 264 |
| 01/11/20 | 3 | -12 |
| 01/12/20 | 4 | 5 |
I started by, Table = VALUES(account_move_line[date]) now I have the date in the first column.
in the second column, I want to have the balance filtered by the account_ id 1.
and in the third column, I want to have the balance filtered by sum of account_id=2 plus id=3.
to look like something like this
| date | balance1 | balance2 |
| 01/01/20 | 159 | 0 |
| 01/02/20 | 145 | |
| 01/03/20 | 0 | 663 |
| 01/04/20 | 0 | |
| 01/05/20 | 0 | |
| 01/06/20 | 365 | |
| 01/07/20 | 0 | 168 |
| 01/08/20 | 0 | 325 |
| 01/09/20 | 0 | |
| 01/10/20 | 138 | |
| 01/11/20 | 0 | 264 |
| 01/12/20 | 0 | -12 |
how can I create these columns?
Hi mohd89ali ,
Please try to create two measures like this:
Balence 1 = CALCULATE ( SUM ( account_move_line[balance ] ), FILTER ( account_move_line, account_move_line[account_id] = 1 && account_move_line[date] = MAX ( 'Table'[date] ) ) )Balence 23 = CALCULATE ( SUM ( account_move_line[balance ] ), FILTER ( account_move_line, account_move_line[account_id] IN { 2, 3 } && account_move_line[date] = MAX ( 'Table'[date] ) ) )For more details, please see the attachment.
1 Reply
Hi mohd89ali ,
Please try to create two measures like this:
Balence 1 = CALCULATE ( SUM ( account_move_line[balance ] ), FILTER ( account_move_line, account_move_line[account_id] = 1 && account_move_line[date] = MAX ( 'Table'[date] ) ) )Balence 23 = CALCULATE ( SUM ( account_move_line[balance ] ), FILTER ( account_move_line, account_move_line[account_id] IN { 2, 3 } && account_move_line[date] = MAX ( 'Table'[date] ) ) )For more details, please see the attachment.