DAX TREATAS filtering by another table to get sales of all products on promotion

Question

How to filter all products on promotion? Say we have two tables Sales and Budget without physical relationship. Here model is simplified and let's assume that it is the case, we cannot create physical relationship. We have to use virtual relationship.

We can see summary:

The two first columns are of the Sales table. The third column BudgetTreats is a measure:

BudgetTreatas =
CALCULATE (
    SUM ( Budget[amount] ),
    TREATAS (
        VALUES ( Sales[id] ),
        Budget[id]
    )
)

Now I would like to resolve two things:

How to make a slicer to filter out only the products (id) which have BudgetTreatas?
How to create a measure for calculating sales but only for products which have a budget? So analogous measure as BudgetTreatas presented above.

And of course sample data: DAX TREATS.pbix

I posted an answer to my question but it is not to show an answer but rather to show working solutions, and give you idea on expected results. I would be grateful for any answer or comments.

References:

The Logic behind the Magic of DAX Cross Table Filtering

Virtual Filters Using TREATAS

How To Use The TREATAS Function - Power BI & DAX

Creating Virtual Relationships Using TREATAS - Advanced Power BI Technique

Przemyslaw Remin Przemyslaw Remin · Accepted Answer · 2019-11-26T13:49:13

Measure calculating Sales filtered by ids in Budget table.

Surprisingly this is not working:

//not working:

SalesFilteredByBudget1 =
CALCULATE (
    [Sales],
    TREATAS ( VALUES ( Budget[id] ), Sales[id] )
)

It seems we need an extra table. If we add to the model a Bridge table with all sales id and connect it to Sales table on id (without connecting it to Budget table!) we may resolve the issue.

//works:

SalesFilteredByBudget2 =
CALCULATE (
    [Sales],
    TREATAS ( VALUES ( Budget[id] ), Bridge[id] )
)

So it seems filters propagate further from tables used in TREATAS( VALUES on the tables connected by physical relations.

If we want to make a measure without Bridge table we can make extra table as a table variable.

// works:
SalesFilteredByBudget3 =
VAR Lineage =
    TREATAS ( VALUES ( Budget[id] ), Sales[id] )
VAR tbl =
    CALCULATETABLE ( Sales, KEEPFILTERS ( Lineage ) )
VAR result =
    CALCULATE ( SUMX ( tbl, [amount] ) )
RETURN
    result

DAX TREATAS filtering by another table to get sales of all products on promotion

1 Answers