不活跃的关系会影响措施 [英] Inactive relationships affecting measures

问题描述

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

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

以下是一些屏幕截图:

关系之前(日期[日期]-表2 [T2Date]):

关系后(日期[日期]-表2 [T2Date]):

由于度量使用不同的关系，所以我需要了解为什么会出现这种差异以及这种关系是如何引起的.

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

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

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

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

  ALLNOBLANKROW =VAR EndServiceDate =MAX(日期[Date])返回计算 (总和(表1 [净额])，筛选 (ALLNOBLANKROW(日期)，Dates [Date]< = EndServiceDate)，Table1 [Flag2] = 1Table1 [Flag] = TRUE()) 

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

请注意， ALL 列中的每一行都已减少 -7,872.01 .这是与<日期>日期表中的任何日期都不匹配的所有<金额>净额值的总和.如果您从 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屋！