Forum Discussion

DSP's avatar
DSP
Frequent Visitor
9 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
    9 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

BILASolution's avatar
BILASolution
Icon for Solution Specialist rankSolution Specialist
9 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