cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
koorosh
Helper IV
Helper IV

create table

Hello Experts, regarding the attached file, how I can have a table with a year column, English country name column, and sum of order quantity from the sales table?

 

https://drive.google.com/file/d/1HmuQ2iUxgIePZVuS7-T9QCzrAa-5pPTy/view?usp=sharing

 

 

2 ACCEPTED SOLUTIONS
aj1973
Community Champion
Community Champion

Hi @koorosh 

What's wrong with the result when you have many to many relatioship?

aj1973_0-1631447069382.png

 

Regards
Amine Jerbi

If I answered your question, please mark this thread as accepted
and you can follow me on
My Website, LinkedIn and Facebook

View solution in original post

@koorosh You need to create a relationship between DimGeography and FactInternetSales, either in the Model view or using DAX.

 

How do we know which Country Name to put into the table? 

You have indicated SalesTerritoryKey is in both tables, so you can relate this with Many to Many - using SINGLE cross filter direction where DimGeography Filters FactInternetSales.

If GeographyKey is in FactInternetSales as 1 to many that would be even better, as you won't be able to get the result by anything smaller than SalesTerritory. Is SalesTerritory smaller than a Country??? If not, you're correct that this won't work. 


Please @mention me in your reply if you want a response.

Check out my Tokyo 2020 report with live stats - KUDOS much appreciated

Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C

I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com

View solution in original post

7 REPLIES 7
AllisonKennedy
Super User
Super User

@koorosh  Access is denied on your Google drive link. Can you paste the first few rows of each table directly into a post here?

 

Do you have a DimDate table? https://excelwithallison.blogspot.com/2020/04/dimdate-what-why-and-how.html 

 

Are your tables related? If so, please paste a screenshot of your Model view as well into your post. 

 

If all the relationships are correct, it should be a simple process of just adding all the fields to a table visualization in Power BI.


Please @mention me in your reply if you want a response.

Check out my Tokyo 2020 report with live stats - KUDOS much appreciated

Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C

I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com

There is no relation between DimGeography and FactinternetSales table. Both tables have SaleTerritoryKey column that could cause many to many relations. But that kind of relationship cannot help to get the result.

koorosh_0-1631444898883.png

 

@koorosh You need to create a relationship between DimGeography and FactInternetSales, either in the Model view or using DAX.

 

How do we know which Country Name to put into the table? 

You have indicated SalesTerritoryKey is in both tables, so you can relate this with Many to Many - using SINGLE cross filter direction where DimGeography Filters FactInternetSales.

If GeographyKey is in FactInternetSales as 1 to many that would be even better, as you won't be able to get the result by anything smaller than SalesTerritory. Is SalesTerritory smaller than a Country??? If not, you're correct that this won't work. 


Please @mention me in your reply if you want a response.

Check out my Tokyo 2020 report with live stats - KUDOS much appreciated

Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C

I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com

View solution in original post

@koorosh  See attached updated file below signature - with your data it's fine many to many by Country, but not by City, State, or PostalCode:

 

AllisonKennedy_0-1631448151385.png

 

 

I have also added a new measure to play with using SUMX in Measure instead of CALCULATE. 


Please @mention me in your reply if you want a response.

Check out my Tokyo 2020 report with live stats - KUDOS much appreciated

Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C

I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com

aj1973
Community Champion
Community Champion

Hi @koorosh 

What's wrong with the result when you have many to many relatioship?

aj1973_0-1631447069382.png

 

Regards
Amine Jerbi

If I answered your question, please mark this thread as accepted
and you can follow me on
My Website, LinkedIn and Facebook

View solution in original post

That was my mistake in summarizecolumns code. Thank you.

koorosh_0-1631448774062.png

 

Now check the link, please. Yes, it has DimDate. 

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Power BI October Update 2021.jpg

Power BI Release

Click here to read more about the October 2021 Release!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Teds Dev Camp Oct. 2021 768x460.jpg

Power BI Dev Camp - October 28th, 2021

Mark your calendars and join us for our next Power BI Dev Camp!