Filter Measure by unrelated table

Comfy

Well-known Member
Joined
Dec 21, 2009
Messages
3,376
Hi Boardies,

So I've finally got the opportunity to mess around with DAX and some real-world data.

I have a FACT table.

DATE | SITE | PRODUCT | NET SALES

and a Dimension Table that only contains unique values (These two tables are not related)

PRODUCT

I also have the following Measure TotalNetSales:=SUM(FACT[NET SALES])

The PRODUCT dimension table is on a worksheet (it is in the Data Model) and I'm hoping I can use it as a filter for a measure.

I have tried this FilteredNetSales:=CALCULATE([TotalNetSales], FILTER(FACT, FACT[Summary] = PRODUCT[Summary]))

But this is wrong, I think because the FILTER is expecting one to evaluate the expression rather than a list???

My pivot has DATE and SITE as row headers.

I'm displaying total sales and want to also display filtered Sales so that I can create a percentage of Sales column.

Any help would be appreciated.

/Comfy
 

Some videos you may like

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.

Matt Allington

MrExcel MVP
Joined
Dec 18, 2014
Messages
1,202
Wy don’t you join the product table to the fact table? That is how it is designed to work.
 

Comfy

Well-known Member
Joined
Dec 21, 2009
Messages
3,376
I tried that but may have/probably made a mistake as it did not give the result that I was expecting.

My end goal is to have 6+ dimension tables that filter 6+ measures.

If I add a relation between the Dimension table [Summary] and the Fact table [Summary] and the measure being:

=CALCULATE([TotalNetSales], FILTER(FACT, 'FACT'[Summary] = DIMENSION[Summary]))

I get the following error:

This formula is invalid or incomplete: 'Calculation error in measure 'FACT'[FilteredSales]: A single value for column 'Summary' in table 'DIMENSION' cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result.'.
 

Comfy

Well-known Member
Joined
Dec 21, 2009
Messages
3,376
If I hard code the filter values I get the required result:

=CALCULATE([TotalNetSales], FILTER('FACT', 'FACT'[Summary] = "ProductA" || 'FACT'[Summary] = "ProductB" || 'FACT'[Summary] = "ProductC" || 'FACT'[Summary] = "ProductD" || 'FACT'[Summary] = "ProductE"))

Is it possible to provide a list of values??
 

Watch MrExcel Video

Forum statistics

Threads
1,089,992
Messages
5,411,712
Members
403,392
Latest member
Faster 72

This Week's Hot Topics

Top