Create a Calculated Table from 4 separate tables

[powerbient]

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.

[powerbient]

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.

[MattAllington]

For what's worth, this is not the best solution. Just because you can create this table in Dax, doesn't mean you should. Best practices is to do the data shaping in power query (get data) and do the modelling only in power pivot. 

Read this article I wrote  here http://exceleratorbi.com.au/shaping-modelling-power-bi/

[powerbient]

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.

[MattAllington]

---

@powerbient wrote:

@MattAllington - since the data model is designed in such a way...

---

What do you mean by this?  The data model is designed by you (the report author and data modeller). You need to make decisions on how to load the data and then how to work with it - this is data modelling. Your statement seems to suggest you don't have any choice in the matter. Maybe I have missed something. 

Here is an article I wrote that you may find useful. 

http://exceleratorbi.com.au/shaping-modelling-power-bi/

[powerbient]

Hi Matt, 

thanks for the reply. I have to go with that model and cant change it. anyways, rather than writing DAX, i used merge functionality in PBI Desktop to achieve the above explained issue.

[MattAllington]

---

@powerbient wrote:

I have to go with that model and cant change it. 

---

This statement does not make any sense.  The data model is what you build in Power BI, so it can be what ever you want it to be - that is what the Power BI Desktop tool is for!  It is irrelevant how the data is shaped at the source - you can change it during load so it meets your needs.