Forum Discussion

powerbient's avatar
powerbient
Frequent Visitor
9 years ago

Create a Calculated Table from 4 separate tables

I am trying to calculate a table by getting columns from 4 different tables as listed in diagram. i am unable to get it through DAX queries i searched online like relatedtables, filter, add columns etc. please help me ASAP in achieving it. 

 

thanks for your help.

Need Help

6 Replies

  • powerbient's avatar
    powerbient
    Frequent Visitor
    9 years ago

    I found the problem and posting the solution here as i didnt get reply from anyone. 

     

    I used the following DAX query.

     

    CalculatedTable = ADDCOLUMNS (
     UserBranch,
    "User", RELATED ( User[UserName]),
    "Order", RELATED ( 'Order'[Ordernumber]),
    "Branch", RELATED(Branch[BranchID])
    )

     

    it threw an error initially because the relationship from Branch to Order table was defined as Many to One rather than default value One to One. once i changed it , it worked perfectly.

      • powerbient's avatar
        powerbient
        Frequent Visitor
        9 years ago

        Yes Matt. you are correct that its not the correct approach.

        Yesterday in the above model, i had Unique Branch ID's so i was able to make One to One relationship between Branch and Order table but today after moving to another server, there were multiple orders from the same Branch and i no longer was able to maintain  One to One relationship between Branch and Order table instead had to shift to Many to One relationship. this way, i cant write a DAX query like yesterday with ADD COLUMN to create a Calculated Table. there you go, i'm back to the same problem.

         

        MattAllington - since the data model is designed in such a way, is there any other solution to overcome this issue to create a Calculated Table? thanks for your help.

         

        ankitpatira   I saw your comments on the can't create relationship between tables because one of the columns must have unique values. i satisfy that condition here but still facing a problem to create a Calculated Table. much appreciate your help.

        anguyen83 MiguelMartinez - can you guys throw some light as well? thank you.