Forum Discussion

koorosh's avatar
koorosh
Post Partisan
4 years ago
Solved

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...
Need Help
  • aj1973's avatar
    aj1973
    4 years ago

    Hi koorosh 

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

     

  • AllisonKennedy's avatar
    AllisonKennedy
    4 years ago

    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. 

AllisonKennedy's avatar
AllisonKennedy
Community Champion
4 years ago

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.

koorosh's avatar
koorosh
Post Partisan
4 years ago

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