DAX TREATAS 按另一个表过滤以获取所有促销产品的销售额 [英] DAX TREATAS filtering by another table to get sales of all products on promotion

问题描述

如何过滤所有促销产品?假设我们有两张表 Sales 和 Budget 没有物理关系.这里的模型被简化了，让我们假设是这样，我们不能创建物理关系.我们必须使用虚拟关系.

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.

我们可以看到总结:

前两列属于 Sales 表.第三列 BudgetTreats 是一个度量:

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]
    )
)

现在我想解决两件事:

1. 如何制作切片器以仅过滤出具有 BudgetTreatas 的产品 (id)?
2. 如何创建用于计算销售额但仅针对有预算的产品的度量?与上面介绍的 BudgetTreatas 类似的措施.

当然还有示例数据:DAX TREATS.pbix

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.

参考资料:

魔术背后的逻辑DAX 交叉表过滤

使用 TREATAS 的虚拟过滤器

如何使用 TREATAS 函数 - Power BI &DAX

使用 TREATAS 创建虚拟关系 - 高级 Power BI 技术

推荐答案

衡量计算按预算表中的 id 过滤的销售额.

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] )
)

看来我们需要一张额外的桌子.如果我们向模型中添加一个包含所有销售 id 的 Bridge 表，并将其连接到 id 上的 Sales 表(而不将其连接到 Budget 表！)，我们可能会解决这个问题.

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] )
)

因此，过滤器似乎从 TREATAS( VALUES 中使用的表传播到通过物理关系连接的表上.

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

如果我们想在没有 Bridge 表的情况下进行测量，我们可以将额外的表作为表变量.

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 按另一个表过滤以获取所有促销产品的销售额的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！