Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago
Solved

Creating a table based on fields from another tables

Dear all,

 

I want to create table based on fields from another tables.The following are the tables.

 

Billing              Collection        Overdue           Bounce

Loankey,           Loankey,            Loankey,            Loankey,

Loanno,            Loanno,             Loanno,              Loanno,

branch,             branch,              branch,               branch,

Monthyr           Monthyr            Monthyr              Monthyr

 

Now i want to create a new table with only 4 fields from all tables.

 

 

Newtable

Loankey,

Loanno,

 branch,

Monthyr

 

Regards,

Joshua.

Need Help
  • Anonymous's avatar
    Anonymous
    8 years ago

    Table = UNION(SUMMARIZE(Billing,Billing[Account No],Billing[Key],Billing[Amount ],Billing[MonthYr ],Billing[Flag]),

    SUMMARIZE(Bounce,Bounce[Account No],Bounce[Key],Bounce[Amount],Bounce[MonthYr],Bounce[Flag]),

    SUMMARIZE(Collection,Collection[AccountNo],Collection[key],Collection[Total],Collection[MonthYr],Collection[Flag]),

    SUMMARIZE(Overdue,Overdue[AccountNo],Overdue[key],Overdue[OVERDUEAmount],Overdue[MonthYr],Overdue[Flag]),

    SUMMARIZE(Register,Register[AccountNo],Register[key],Register[PRINCIPALAMOUNT ],Register[MonthYr ],Register[Flag]))

5 Replies

  • Seward12533's avatar
    Seward12533
    Icon for Solution Sage rankSolution Sage
    8 years ago
    Create Table from Modeling tab.

    New table = VALUES(billing[ColName])
    • Anonymous's avatar
      Anonymous
      Not applicable
      8 years ago

      Dear Seward,

       

      Thanks for ur reply.

      New table =VALUES(billing[Loankey],overdue[Loankey],bounce[Loankey],collection[Loankey])

      will it works because the Loankey in New table must contain data loankey of all four tables.

    • Anonymous's avatar
      Anonymous
      Not applicable
      8 years ago

      Dear Seward ,

      i will explain with an example 

       

      Billing:

      Branch           Loankey             Loanno     Monthyr    amount  

      Bangalore      124Jun-18          124           Jun-18        2200

       

       

      Collection:

      Branch           Loankey             Loanno     Monthyr    amount  

      Bangalore      124Aug-18        124           Aug-18        1800

       

       

      Now i want to create a new table

       

      NewTable:

      Branch           Loankey             Loanno     Monthyr    amount  

      Bangalore      124Jun-18          124           Jun-18        2200

      Bangalore      124Aug-18        124           Aug-18        1800

       

      Regards,

      Joshua.

      • Seward12533's avatar
        Seward12533
        Icon for Solution Sage rankSolution Sage
        8 years ago
        Would not use values for that. Can merge tables in Dax I usually do that and most of the data shaping inside power query. You would use Combine queries merge.

        https://youtu.be/A1OgxKZmbqg

  • Anonymous's avatar
    Anonymous
    Not applicable
    8 years ago

    Table = UNION(SUMMARIZE(Billing,Billing[Account No],Billing[Key],Billing[Amount ],Billing[MonthYr ],Billing[Flag]),

    SUMMARIZE(Bounce,Bounce[Account No],Bounce[Key],Bounce[Amount],Bounce[MonthYr],Bounce[Flag]),

    SUMMARIZE(Collection,Collection[AccountNo],Collection[key],Collection[Total],Collection[MonthYr],Collection[Flag]),

    SUMMARIZE(Overdue,Overdue[AccountNo],Overdue[key],Overdue[OVERDUEAmount],Overdue[MonthYr],Overdue[Flag]),

    SUMMARIZE(Register,Register[AccountNo],Register[key],Register[PRINCIPALAMOUNT ],Register[MonthYr ],Register[Flag]))