Forum Discussion

DSP's avatar
DSP
Frequent Visitor
8 years ago
Solved

DAX query with filter from parallel dimension table

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...
Need Help
  • v-ljerr-msft's avatar
    v-ljerr-msft
    8 years ago

    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. :smileyhappy:

     

    Table =
SUMMARIZE (
    FILTER (
        DimCustomer,
        RELATED ( DimGeography[EnglishCountryRegionName] ) = "Canada"
    ),
    DimCustomer[LastName-FirstName],
    "Sales", [Total Sales]
)

     

     

    Regards

v-ljerr-msft's avatar
v-ljerr-msft
Microsoft Employee
8 years ago

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. :smileyhappy:

 

Table =
SUMMARIZE (
    FILTER (
        DimCustomer,
        RELATED ( DimGeography[EnglishCountryRegionName] ) = "Canada"
    ),
    DimCustomer[LastName-FirstName],
    "Sales", [Total Sales]
)

 

 

Regards

DSP's avatar
DSP
Frequent Visitor
8 years ago

Many thanks for that.

 

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