PowerBI DAX : Creating Measure which accepts partial visual filters from same table

Question

我有一个事实表和一个日历表。日历表有一个 bool 列，表示一天是否是假期。

我不想创建一个度量:AllSales，它会忽略 Holiday 上的任何过滤器，但尊重所有其他 Calendar 列上的过滤器。稍后查看应用的视觉级别过滤器。

除总金额外，以下措施有效:

AllSales = CALCULATE(SUM(FactSales[Sales]); ALL('Calendar'[Holiday]); ALL('Calendar'[DateKey]))

结果见下图。个别周还可以，因为这些都可以，但请注意总和为错误总数的年份。酒吧聊天显示正确，但矩阵表中的总数是错误的。由于视觉级别筛选器 Calendar[DateKey] 移除了 2019 年的第一周，AllSales 的总数应该是 700，但它仍然计入总数。

总结一下:

• Calendar[DateKey] > "2019 year 01 week
视觉级别过滤器
• Calendar[Holiday] = False 上的视觉级别过滤器
• 衡量 AllSales 应该遵守 DateKey 过滤器
• 衡量 AllSales 应忽略所有假日过滤器
• 视觉效果按 Calendar[Year]、Calendar[Week] 列排序

如果我删除 ALL(Calendar[DateKey]) 它就像我的 ALL(Calendar[Holiday]) 过滤器被忽略并且 AllSales 等同于原始数据列 Sales。

如果您不想使用这里的数据，可以使用两个 M 查询来创建 Calendar 和 FactSales 表。只需通过 DateKey 1:* 单向链接它们即可。

创建两个新查询并将下面的代码粘贴到高级编辑器(M 语言编辑器)中

日历查询:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjIwtNQ1MAQiJR0wB0iBmCFFpalKsTpICoxQFbgl5hSjqTAmqMIEocIIuwpT3CqMDJAcamSA6VCoAiNUBViMMCaowgShAoczTLGqiAUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [DateKey = _t, year = _t, Week = _t, Holiday = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"DateKey", type date}, {"year", Int64.Type}, {"Week", Int64.Type}, {"Holiday", type logical}}),
    #"Renamed Columns" = Table.RenameColumns(#"Changed Type",{{"year", "Year"}})
in
    #"Renamed Columns"

FactSales 查询:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQwUNJRMjIwtNQ1MAQipVgdDEEjbILG2ARNsAmaoggaGWCxCCpohE3QGJugCTZBoEWxAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Sales = _t, DateKey = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Sales", type number}, {"DateKey", type date}})
in
    #"Changed Type"

我得出的结论是，DAX/TabularModel 不能很好地处理同一张表中的多个切片器/过滤器，我应该将它们分开。我仍然想知道是否有人有针对此问题的 DAX 解决方案。

Answer 1

因此，当您构建视觉对象时，会创建 DAX 查询以返回结果集，然后绘制该结果集。您可以在下面看到查​​询的摘录。在此之后还有更多内容，但我们拥有了解您所看到的行为所需的一切。

DEFINE
  // This is a table-valued variable, which holds all dates which will be in context.
  // This is based on your visual filter.
  VAR __DS0FilterTable = 
    FILTER(
      KEEPFILTERS(VALUES('Calendar'[DateKey])),
      'Calendar'[DateKey] >= (DATE(2019, 1, 3) + TIME(0, 0, 1))
    )

  // This is a table-valued variable which holds the 1-row, 1-column table based on
  // 'Calendar'[Holiday] holding the value FALSE. This is from your slicer
  VAR __DS0FilterTable2 = 
    TREATAS({FALSE,
      BLANK()}, 'Calendar'[Holiday])

  // This builds up your resultset
  VAR __DS0Core = 
    // SUMMARIZECOLUMNS lists your grouping columns, applies filters, and projects
    // measures onto your resultset
    SUMMARIZECOLUMNS(
      // This rollup defines the grouping columns, whose values are shown on your visual.
      // The rollup defines which columns get subtotals, which means we calculate an
      // additional row in the resultset with filter context cleared from the column
      // which is rolling up. In this case, we'll have a row with no Year context,
      // but with Week context, a row with no Week context, but with Year context, and
      // a row with no context on either.
      ROLLUPADDISSUBTOTAL('Calendar'[Year], "IsGrandTotalRowTotal", 'Calendar'[Week], "IsDM1Total"),

      // I'm separating out the filter args here. These are evaluated in a logical
      // AND. The first includes all dates that pass your filter on the visual.
      // The second includes only 'Calendar'[Holiday]=FALSE. The logical AND of these
      // includes:
      //   {2019-01-04, 2019-01-05, 2020-01-02, 2020-01-03, 2020-01-04, 2020-01-05}
      // Note that we're missing 2020-01-01, which passes the date test, but is a holiday.
      // This is the context for evaluating our measures.
      __DS0FilterTable,
      __DS0FilterTable2,
      // end of filter args

      "Sales_Amount", 'FactSales'[Sales Amount],
      "All_Sales", 'FactSales'[All Sales],
    )

因此，根据评估方式，当它到达您的度量值时，日期的筛选上下文为:

<whatever is on your visual at that level>

INTERSECT

{2019-01-04, 2019-01-05, 2020-01-02, 2020-01-03, 2020-01-04, 2020-01-05}

这是我们评估的过滤器上下文:

AllSales =
CALCULATE (
  SUM ( FactSales[Sales] );
  ALL ( 'Calendar'[Holiday] );
  ALL ( 'Calendar'[DateKey] )
)

因此，如果没有您的 ALL ( 'Calendar'[DateKey] )，您总和的筛选上下文不包括 2020-01-01。不幸的是，您的 'Calendar'[DateKey] > 2019-01-03 过滤器也被此 ALL 清除，这解释了您的总计问题。总计没有 Year 上下文(基于 SUMMARIZECOLUMNS 中的汇总)，没有 Week 上下文(基于相同的)，然后基于您的度量，没有 Holiday 上下文和 DateKey 上下文。

它适用于您的柱形图的原因是没有要显示的总计。

现在，一个可能不令人满意的解决方案是定义一个新列 YearWeek 并对其进行过滤，而不是对“日历”[DateKey] 进行过滤，因为它看起来非常相似。

所以:

//column
'Calendar'[YearWeek] = 'Calendar'[Year] * 100 + 'Calendar'[Week]

//measure:
All Sales =
CALCULATE (
  SUM ( 'FactSales'[Sales] );
  ALL ( 'Calendar'[Holiday] )
)

然后你可以在 'Calendar'[YearWeek] > 201901 上定义一个过滤器。这在逻辑上是等价的，但不是 DAX 等价的。您的查询现在看起来像这样:

DEFINE
  // This is now on 'Calendar'[YearWeek], instead of 'Calendar'[DateKey]
  VAR __DS0FilterTable = 
    FILTER(
      KEEPFILTERS(VALUES('Calendar'[YearWeek])),
      'Calendar'[YearWeek] > 201901
    )

  // same as before
  VAR __DS0FilterTable2 = 
    TREATAS({FALSE,
      BLANK()}, 'Calendar'[Holiday])

  // Textually same as before, but different context
  VAR __DS0Core = 
    SUMMARIZECOLUMNS(
      ROLLUPADDISSUBTOTAL('Calendar'[Year], "IsGrandTotalRowTotal", 'Calendar'[Week], "IsDM1Total"),

      // Here are the filters again:
      // Our intersection of 'Calendar'[Holiday]=FALSE and 'Calendar'[YearWeek]>201901.
      // The 'Calendar'[YearWeek] filter is: {201902, 202001, 202002}
      // so now our filter context doesn't explicitly exclude the date 2020-01-01, which
      // was the problem above.
      __DS0FilterTable,
      __DS0FilterTable2,
      // end of filters

      "Sales_Amount", 'FactSales'[Sales Amount],
      "All_Sales", 'FactSales'[All Sales]
    )

...

基于上面的过滤器上下文，该度量工作得很好。

那么，下面两种情况有什么区别:

1. 按“日历”[假日] 和“日历”[DateKey] 过滤
2. 按“日历”[假日] 和“日历”[年周] 过滤

它们是同一张表上的两组过滤器。不同之处在于 'Calendar'[Holiday] 和 'Calendar'[DateKey] 处于同一粒度。它们的交集明确排除了您想要的日期。所以你需要一个额外的 ALL 来覆盖它。不过，ALL 过于宽泛，这会影响您的总计。

'Calendar'[Holiday] 和 'Calendar'[YearWeek] 的粒度不同。如果在单个 YearWeek 中有整整一周的天数都是“Calendar”[Holiday]=TRUE，那么您会遇到与上述相同的问题，因为“Calendar”[Holiday] 上的过滤器会无意中删除一个您要包括的整个“日历”[YearWeek]。

所有这一切的关键在于 DAX 过滤器上下文只是列中文字值的列表。重点很重要。在回答这个问题时我绞尽脑汁，但将其分解为字面值(value)表让我回到了正轨。

这是一个非常好的问题，感谢您提出这个问题。它让我深入了解了 DAX 的一些细微差别，我最近不必考虑太多。您的直觉会准确判断事物的行为方式。