DAX calculated column for related table with different grain

Question

I want to create a TRUE/FALSE DAX calculated column on my sales table to filter for "Sales of Orders Containing Apples". Simplified schema:

Product Table:

| ProductKey | Product |
|------------|---------|
| 1          | Apples  |
| 2          | Milk    |

Sales table:

| SaleID | ProductKey | Sale Amount |
|--------|------------|-------|
| A      | 1          | £2    |
| B      | 2          | £4    |
| C      | 1          | £8    |
| C      | 2          | £16   |

I then apply a slicer/filter in Power BI/Power Pivot in Excel. The end result should be:

| SaleID | Sales of Orders Containing Apples |
|--------|------------|
| A      | £2         |
| C      | £24        |

I don't want to create a "Sales of Orders Containing Apples" measure. My real model has many measures and I want to slice/dice them all by this new attribute.

I can easily push this down to the data warehouse tier, but how is this achieved in DAX?

Andy Jones, is there not a relationship between Product Table and Sales Table? In such a case, simply filtering the Product table down to Apples will also filter your Orders table to only the orders whose Product Key corresponds to 'Apples.' — Ryan B.
Thanks Ryan. There is a relationship. However, a direct product filter for "Apples" would give me sales of £8 for SaleID = "C", not £24 as required. — Andy Jones
Aha, I overlooked that. Well, then I would start to recommend a bridge table but Greg's already cracked one out. — Ryan B.

greggyb greggyb · Accepted Answer · 2019-09-16T17:11:27

Sales = SUM ( 'Sales'[Sale Amount] )

Sales of orders containing selected product =
VAR OrdersInContext = VALUES ( 'Sales'[SaleID] )
RETURN
  CALCULATE (
    [Sales],
    ALL ( 'Product' ),
    OrdersInContext
  )

This is actually a lot more general than what you've asked for. Hopefully the definition of [Sales] is self-explanatory.

[Sales of orders containing selected product] first grabs a list of all 'Sales'[SaleID] values in the current context, which includes context from all dims, including 'Product'. In the sample case (screenshot below, from your question), the context is 'Product'[Product]="Apples", so our variable contains 'Sales'[SaleID] IN {"A", "C"}.

Next we use CALCULATE to manipulate the context within which to evaluate [Sales]. We clear all context on 'Product', but apply the context of 'Sales'[SaleID] stored in our variable, OrdersInContext.

Understanding that, and also understanding that the args to CALCULATE are evaluated independently, then intersected, we can shorten the definition to this:

Sales of orders containing selected product =
CALCULATE ( 
  [Sales],
  ALL ( 'Product' ),
  VALUES ( 'Sales'[SaleID] )
)

I show this second, just because I've found that most people find the style with the variable to be easier to understand. It is semantically equivalent to the first version.

And here it is in action:

EDIT: Based on comments, we want to have this sort of slicer affect all measures for the fact table. I'm assuming the fact table interacts with more dims than just 'Product'. Either way, though, the following model would do it:

Tables
- 'Product':: the product dimension shared
- 'ProductSales':: a bridge table of unique pairs of (ProductKey, SaleID)
- 'SaleIDs':: a table of only unique SaleIDs
- 'Sales':: the original fact table shared
Relationships:
- 'Product'[ProductKey] -1:N-> 'ProductSales'[ProductKey]
- 'ProductSales'[SaleID] <-N:1-> 'SaleIDs'[SaleID]
- 'SaleIDs'[SaleID] -1:N-> 'Sales'[SaleID]

See this in action below:

Model diagram:

And here a simple measure (no logic besides sum) showing correct aggregates in a report for you:

DAX calculated column for related table with different grain

1 Answers