Create calcualated table

[CloudMonkey]

Hi,

Please can you tell me how to combine two tables together as below? (I mean in the data tables backend rather than in a visualisation)

"Transactions" Table

Transaction ID, Branch ID

"Branches" Table

Branch ID, Area

"Combined" Table

Area, Branch ID, Total number of transactions (count of rows in "Transactions" table)

Thanks for your help,

CM

[Anonymous · Accepted solution]

Hi @CloudMonkey

There are many ways to do this.

If you want to do this in Power Query:

1. In Power Query ,Merge both tables and then apply Group by transfomation ( Transform -> Group by)

If you want to do this in DAX :

1. Define relationship between the tables.

2. Create a measure Count(Transactionid) and pull Branch id, area, Measure to get this data.

Thaks

Raj

[v-yulgu-msft · Accepted solution]

Hi @CloudMonkey,

In Power Query, you can combine these two tables via "Merge Query". The general steps are: Merge as new queries -> Group by -> Expand columns -> Remove duplicates.

If you want to create a calculated table via DAX, please refer to:

Combined table =
ADDCOLUMNS (
    SELECTCOLUMNS (
        Branches,
        "BranchID", Branches[Branch ID],
        "Area", Branches[Area]
    ),
    "Count Transactions", CALCULATE (
        COUNT ( Transactions[Transaction ID] ),
        FILTER ( Transactions, Transactions[Branch ID] = EARLIER ( [BranchID] ) )
    )
)

Best regards,

Yuliana Gu

[Anonymous · Accepted solution]

Hi @CloudMonkey

There are many ways to do this.

If you want to do this in Power Query:

1. In Power Query ,Merge both tables and then apply Group by transfomation ( Transform -> Group by)

If you want to do this in DAX :

1. Define relationship between the tables.

2. Create a measure Count(Transactionid) and pull Branch id, area, Measure to get this data.

Thaks

Raj