Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
107 | |
98 | |
78 | |
66 | |
53 |
User | Count |
---|---|
144 | |
104 | |
100 | |
86 | |
64 |