Forum Discussion
New Table from existing table plus calculated data from another table
Hi,
i have the following problem with 2 Tables:
Table 1 is a table with yearly dates, e.g.
Start | End
01/01/22 | 02/01/22
02/01/22 | 02/01/22
03/01/22 | 02/01/22
Table 2 consists of parking data
Entry | Exit | Parkingarea | Tickets
01/01/22 12:00 | 01/01/22 12:00 | P1 | 1
02/01/22 09:00 | 01/01/22 12:00 | P3 | 1
01/01/22 07:15 | 01/01/22 12:00 | P1 | 2
01/01/22 12:00 | 01/01/22 12:00 | P4 | 1
So like to create a new table wicht consists of table 1 and calculated data from table2, like this
TableNew = SELECTCOLUMNS(table1, "Start" , [Start], "End" , [End], SUMMARIZECOLUMNS(table2, [tickets], filter(table2, table2[Parkingarea]="P1"), FILTER (ALLSELECTED(table2), table2[Entry] <= table1[start]
&& table2[Exit] >= table1[start])))
and this for all parkingareas (>25). Does anybody know a solution?
Thanks?
- Anonymous3 years ago
Hi roadcyclist,
You can try to use the following calculated table formula to create a new table based two table fields and conditions:
TableNew = SELECTCOLUMNS ( table1, "Start", [Start], "End", [End], "tickets", CALCULATE ( SUM ( [tickets] ), FILTER ( ALLSELECTED ( table2 ), table2[Parkingarea] = "P1" && AND ( table2[Entry] <= [End], table2[Exit] >= [Start] ) ) ) )Regards,
Xiaoxin Sheng
1 Reply
- AnonymousNot applicable
Hi roadcyclist,
You can try to use the following calculated table formula to create a new table based two table fields and conditions:
TableNew = SELECTCOLUMNS ( table1, "Start", [Start], "End", [End], "tickets", CALCULATE ( SUM ( [tickets] ), FILTER ( ALLSELECTED ( table2 ), table2[Parkingarea] = "P1" && AND ( table2[Entry] <= [End], table2[Exit] >= [Start] ) ) ) )Regards,
Xiaoxin Sheng