Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount.
Register nowThe Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.
Newbie alert!
I am looking at tables from one of the Adventureworks databases.
I am trying to produce a table which lists customers from Canada with the total sales for each customer.
The following code produces a list of all customers:
EVALUATE SUMMARIZE( DimCustomer, DimCustomer[LastName-FirstName], "Sales",[TotalSales] )
How do I filter the table down to just Canadian customers?
I have code
DimGeography[EnglishCountryRegionName]="Canada"
but I can't see how to use it to introduce the necessary filter.
There is, of course, no relationship between DimCustomer and DimGeography (except through the sales table).
If there were a country key in the customer table, I could use that - but there isn't.
I know I'm missing something absurdly obvious - could some kind soul point this out, please?
Solved! Go to Solution.
Hi @DSP,
I'm not sure if we're using different Adventureworks databases. I have tested with AdventureWorksDW2014 and AdventureWorksDW2012. And there is a relationship between DimCustomer and DimGeography with the GeographyKey column.
And I can use the formula below to create the summarize table.
Table = SUMMARIZE ( FILTER ( DimCustomer, RELATED ( DimGeography[EnglishCountryRegionName] ) = "Canada" ), DimCustomer[LastName-FirstName], "Sales", [Total Sales] )
Regards
Hi @DSP,
I'm not sure if we're using different Adventureworks databases. I have tested with AdventureWorksDW2014 and AdventureWorksDW2012. And there is a relationship between DimCustomer and DimGeography with the GeographyKey column.
And I can use the formula below to create the summarize table.
Table = SUMMARIZE ( FILTER ( DimCustomer, RELATED ( DimGeography[EnglishCountryRegionName] ) = "Canada" ), DimCustomer[LastName-FirstName], "Sales", [Total Sales] )
Regards
Many thanks for that.
The table relationships were indeed set up as per your diagram - which I'd somehow failed to notice!
Hi @DSP
Try this...
Customer Sales =
SUMMARIZE
(
CALCULATETABLE(FactInternetSales;DimGeography[EnglishCountryRegionName] = "Canada");
DimCustomer[LastName - FirstName];"Sales";[Total Sales]
)
I hope this helps
Best Regards
BILASolution
User | Count |
---|---|
136 | |
70 | |
69 | |
54 | |
52 |
User | Count |
---|---|
207 | |
94 | |
64 | |
61 | |
57 |