Forum Discussion
How To Create a Column That Puts CustomerName + City + Country From Two Different Table?
Hi Guys,
I have more tables in the report, but the four tables I want use for this calculations are Facttable, DimCustomer, DimOrder and DimDate. The CustomerName field in the customer table from fiscalyear 2020 and after contains "CustomerName + City + Country", however before fiscalyear 2020 this field contains only CustomerName.
What I want is to create a column in one of these tables or in a separate table that contains all the customers from fiscalyear 2020 and after which is (CustomerName + City + Country) PLUS all the customer before fiscalyear 2020 with their "CustomerName, City and Country", city and country from DimOrder. Please take a look at the screenshot.
I already I tried to left join DimCustomer with DimOrder in Power Query, it was multiplying and couldn't figure it out.
Any suggestion or solution will be helpful.
Thanks in advance!
9 Replies
Mohammadwazeri It's better performance and best practice to keep Customer Name in separate column than Customer City, Country. Is the data as clean as your screenshot? If so, I recommend using the 'Split Column' in Power Query to make the 2020 Customer data just have Customer Name.
For the non 2020 Customer - Where do you want to get the City from? Is that meant to pull from the DimOrder table (which is actually a Fact table level). I suspect a single customer might have more than one City in the Orders table? I do not recommend storing the Order City, Country in the Customer table.
If you build the star schema correctly in Power BI data model, you can create all the table visuals you want/need with the Customer Name, City, Country but leave that data in the respective tables.
Hi Allison,
Thanks for replying.
Yes, the data is clean, it is good to keep customer in separate table, but the leadership wants to see "CustomerName + City + Country".
I want to get the city and country from DimOrder table. That is the REASON leadership wants to see the global customer city and country to find out in how many city and county we are sending our product.
For instance; we are sending our product for customer A in multiple city see the below screenshot, same thing with customer B and all other customers.
Is there is any link to guide to get through creating Star Schema?
Mohammadwazeri Can you share a screenshot of your data model relationships view please?
Your Customer table should be related to your DimOrder Table with a one to many relationship. I suggest using a data model that looks similar to this:
For your DimCustomer table, you need a column that just has Customer name only. As per my previous reply, you can do this in Power Query:
Click Transform Data
Select the DimCustomer Query
In the Transform Column tab, click 'Split Column' > By Delimiter.
Type - for the delimiter.
Rename Customer.1 to Customer Name.
Now that you have the column and relationships, you have two options:
OPTION A) Table visual
Create a table visualization with:
DimCustomer[Customer Name]
DimOrder[City]
DimOrder[Country]
DimOrder[OrderID] (set this to Count or Don't Summarize, depending on what you want to see)
OPTION B) Calculated column
This is only if you need the info formatted with commas in one handy column:
Customer, City, Country = COMBINEVALUES(", ", RELATED(DimCustomer[Customer Name]), DimOrder[City], DimOrder[Country] )
Hope that helps - advise with screenshots if it doesn't, and paste the data from your sample in as tables (not screenshots) so we can easily create a sample file for you if needed.
Hi, Mohammadwazeri ;
If i understand right , you could create a column by dax as follows:
Column = IF(YEAR(RELATED('FactTable'[TaxDate]))<2020,[CustName]&"-"&RELATED('DimOrder'[City])&","&RELATED('DimOrder'[Country]),[CustName])The final output is shown below:
If not correct, can you share more datails about it?
Best Regards,
Community Support Team_ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.Hi,
Thanks for replying!
It seems to be the correct column I suppose to create in my report, but when I use "RELATED" some how it the Facttable and DimOrder table are not showing up during writing the DAX. Also I changed the relationship directions from single to both, but no result.
Hi， Mohammadwazeri ；
"RELATED" does not apply to many-to-many relationships. Can you share the relationship diagram of the actual case? Or share a simple file that is closer to your data, so it is more convenient for me to test and solve it.
Best Regards,
Community Support Team_ Yalan Wu
