Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

Reply
DSP
Frequent Visitor

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?

1 ACCEPTED SOLUTION
v-ljerr-msft
Microsoft Employee
Microsoft Employee

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.

 

relationship.PNG

 

And I can use the formula below to create the summarize table. Smiley Happy

 

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

 

 

Regards

View solution in original post

3 REPLIES 3
v-ljerr-msft
Microsoft Employee
Microsoft Employee

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.

 

relationship.PNG

 

And I can use the formula below to create the summarize table. Smiley Happy

 

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!

BILASolution
Solution Specialist
Solution Specialist

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

Helpful resources

Announcements
Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Jan NL Carousel

Fabric Community Update - January 2025

Find out what's new and trending in the Fabric community.