Using TREATAS to get Measure from Another Table

Question

I'm back with another issue. I have a sales table with transaction details on the products purchased. I also have a table with warehouse inventory information for each product. I'm trying to get the count of products purchased in a Table visualization with columns from the warehouse inventory table.

I tried both of the measures below, but they both return the total Count for each row rather than sliced by product. Any help would be greatly appreciated!

NumProductsfromSales1 = calculate([Count], treatas(values('Sales'[Product]), 'Inventory'[Product]))

NumProductsfromSales2 = 
var lineage = treatas(values('Sales'[Product]), 'Inventory'[Product])
var tbl = calculatetable('Inventory Detail', KEEPFILTERS(lineage))
var result = calculate(sumx(tbl, [Count]))
return  result

Count is a measure defined as the sum of product count for each transaction within a certain distance. I guess it makes sense it wouldn't work because of the distance filter. I'm not sure how to get around that though. """[Count] = calculate(sum('Sales'[Product Count]) , filter('Sales', [Sales Distance] < [Mile Input]))""" — pmtthee

Alexis Olson Alexis Olson · Accepted Answer · 2020-05-06T19:58:31

From this source, we see TREATAS works as follows.

[Filtered Measure] :=
CALCULATE (
    <target_measure>,
    TREATAS (
        VALUES ( <lookup_granularity_column> ),
        <target_granularity_column>
    )
)

is equivalent to

[Filtered Measure] :=
CALCULATE (
    <target_measure>,
    INTERSECT (
        ALL ( <target_granularity_column> ),
        VALUES ( <lookup_granularity_column> )
    )
)

The important part is the ALL function. That's why you're losing the filter context from the rows in the visual.

I'm not sure if this is the most efficient solution, but I think if you add Inventory as a filter table to your first attempt, it should maintain the filter context on that table from the row in the visual.

NumProductsfromSales1 =
CALCULATE (
    [Count],
    'Inventory',
    TREATAS ( VALUES ( 'Sales'[Product] ), 'Inventory'[Product] )
)

Edit: Regarding your comment, try the following:

a =
VAR top5prod =
    SELECTCOLUMNS (
        TOPN (
            5,
            SUMMARIZE ( Sales, Sales[Product], "Count", [Product Count] ),
            [Count]
        ),
        "Product", Sales[Product]
    )
RETURN
    CALCULATE (
        [Product Count],
        FILTER ( 'Inventory', 'Inventory'[Product] IN top5prod )
    )

Using FILTER isn't as efficient as TREATAS but see if it works.

It's very difficult to answer this sort of question without having anything reproducible to work with.

Using TREATAS to get Measure from Another Table

1 Answers