Forum Discussion

roadcyclist's avatar
roadcyclist
New Member
3 years ago
Solved

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?

  • Anonymous's avatar
    Anonymous
    3 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

  • Anonymous's avatar
    Anonymous
    Not 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