Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago
Solved

Create calcualated table

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

Need Help
  • Anonymous's avatar
    Anonymous
    8 years ago

    Hi Anonymous

     

    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's avatar
    v-yulgu-msft
    8 years ago

    Hi Anonymous,

     

    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

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    8 years ago

    Hi Anonymous

     

    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's avatar
    v-yulgu-msft
    Microsoft Employee
    8 years ago

    Hi Anonymous,

     

    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