Forum Discussion
create table
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.
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.
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.
That was my mistake in summarizecolumns code. Thank you.
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.
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:
I have also added a new measure to play with using SUMX in Measure instead of CALCULATE.