影响度量的非活动关系 [英] Inactive relationships affecting measures

问题描述

我有以下表格和我们 pbix 报告中的关系:

出于一些明显的原因，我需要在 Dates[date] 和 Table2[T2Date] 之间建立关系(非活动).但是，这样做会导致数据波动，以衡量表 1 中的总金额".

以下是一些截图:

之前的关系(日期[日期] - Table2[T2Date]):

关系后(Dates[date] - Table2[T2Date]):

我需要了解为什么会出现这种差异以及这种关系是如何导致这种差异的，因为该度量使用了不同的关系.

作为参考，我附上 pbix 报告.

这里要注意的关键是，当您将 Table2 附加到 Dates 时，因为 Table2 包含 T2Date与任何 Date[date] 不匹配的值，这会在 Dates 中创建一个额外的行，其中包含空白日期，您可以在 6 上的过滤器中注意到.存在该关系(活动或非活动)的年份.过滤掉 6 中的那个空白.Year 过滤器会起作用，除了在您的度量中，您使用 ALL(Dates) 来去除对该表完成的所有过滤.

有多种方法可以解决这种差异，最简单的方法是将 ALL 替换为 ALLNOBLANKROW.如果您使用 ALLSELECTED，它也可以与在 6 上的报告级过滤器中过滤掉空白一起使用.年份.

清理一些与此上下文无关的项目并将ALL更改为ALLNOBLANKROW，您的总度量可以更简单地写为:

ALLNOBLANKROW =VAR EndServiceDate =MAX(日期[日期])返回计算 (SUM(表1[净额])，筛选 (ALLNOBLANKROW(日期)，日期[日期] <= EndServiceDate),表 1 [标志 2] = 1，表 1[标志] = TRUE ())

没有的结果6.Year 过滤器和两个度量，一个使用 ALL，一个使用 ALLNOBLANKROW:

请注意，ALL 列中的每一行都减少了 -7,872.01.这是与 Dates 表中的任何日期都不匹配的所有 Net Amount 值的总和.如果您删除从 Dates[date] 到 Table2[T2Date] 的关系，则空白行不再存在，这两个都将匹配 ALLNOBLANKROW 版本.

I have the following tables & relationships in our pbix report:

For some obvious reasons, I need to have a relationship (non-active) between Dates[date] and Table2[T2Date]. However, doing so causes data fluctuation to measure 'Total Amount' in Table1.

Here are some screenshots:

Before Relationship (Dates[date] - Table2[T2Date]):

After Relationship (Dates[date] - Table2[T2Date]):

I need to understand why this difference is coming up and how the relationship is causing it since the measure uses a different relationship.

For reference, I am attaching the pbix report.

https://drive.google.com/open?id=1XknisXvElS6uQN224bEcZ_biX7m-4el4

Any help would be appreciated :)

解决方案

The link that @MikeHoney gives has really useful information on the subtleties of relationships and does relate to this problem (do watch it!), but this issue is not ultimately related to bidirectional filtering in particular. In fact, I can reproduce it with this simplified relationship structure:

The key thing to note here is that when you attach Table2 to Dates, since Table2 contains T2Date values that don't match to any Date[date], this creates an extra row in Dates with a blank date which you can notice in your filter on 6. Year when that relationship exists (active or inactive). Filtering out that that blank in the 6. Year filter would work, except that in your measure, you use ALL(Dates) to strip all filtering done on that table.

There are multiple ways to resolve this discrepancy, the easiest being replacing ALL with ALLNOBLANKROW. If you used ALLSELECTED that would also work in conjunction with filtering out blanks on your report-level filter on 6. Year.

Cleaning up some items not relevant in this context and changing ALL to ALLNOBLANKROW, your total measure can be more simply written as:

ALLNOBLANKROW =
VAR EndServiceDate =
    MAX ( Dates[Date] )
RETURN
    CALCULATE (
        SUM ( Table1[Net Amount] ),
        FILTER (
            ALLNOBLANKROW ( Dates ),
            Dates[Date] <= EndServiceDate
        ),
        Table1[Flag2] = 1,
        Table1[Flag] = TRUE ()
    )

Results with no 6. Year filter and with two measures, one using ALL and one using ALLNOBLANKROW:

Notice that every row in the ALL column has been reduced by -7,872.01. This is the sum of all the Net Amount values that don't match to any dates in the Dates table. If you remove the relationship from Dates[date] to Table2[T2Date] then the blank row no longer exists and both of these will match the ALLNOBLANKROW version.

这篇关于影响度量的非活动关系的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！