Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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 |
---|---|
101 | |
90 | |
78 | |
70 | |
69 |
User | Count |
---|---|
110 | |
96 | |
95 | |
74 | |
71 |