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.

参考文献：

< a href = http://mdxdax.blogspot.com/2011/03/logic-behind-magic-of-dax-cross-table.html rel = nofollow noreferrer> 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的桥表添加到模型中，然后将其连接到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（值 c>中使用的表）进一步传播。

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屋！