Filter Measure by unrelated table

[Comfy]

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

 

[Matt Allington]

Wy don’t you join the product table to the fact table? That is how it is designed to work.

 

[Comfy]

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]

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