DAX query with filter from parallel dimension table

[DSP]

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?

[v-ljerr-msft · Accepted 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

[DSP]

Many thanks for that.

The table relationships were indeed set up as per your diagram - which I'd somehow failed to notice!

[BILASolution]

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