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 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?

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

3 Replies

  • BILASolution's avatar
    BILASolution
    Solution Specialist
    8 years ago

    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

  • 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!