首页 文章

在Dax中有效计算DISTINCTCOUNT的平均值？

提问于
浏览 次
3

我正在尝试计算DAX中的业务逻辑，这已经证明是资源非常庞大且复杂的 . 我有一个非常大的PowerPivot模型（称之为“销售”），有许多尺寸和尺寸 . 销售模型的简化视图：

+-------+--------+---------+------+---------+-------+
| State |  City  |  Store  | Week | Product | Sales |
+-------+--------+---------+------+---------+-------+
| NY    | NYC    | Charlie |    1 | A       | $5    |
| MA    | Boston | Bravo   |    2 | B       | $10   |
| -     | D.C.   | Delta   |    1 | A       | $20   |
+-------+--------+---------+------+---------+-------+

基本上我要做的是按商店和周计算产品的DISTINCTCOUNT：

SUMMARIZE(Sales,[Store],[Week],"Distinct Products",DISTINCTCOUNT([Product]))

+---------+------+-------------------+
|  Store  | Week | Distinct Products |
+---------+------+-------------------+
| Charlie |    1 |                15 |
| Charlie |    2 |                 7 |
| Charlie |    3 |                12 |
| Bravo   |    1 |                20 |
| Bravo   |    2 |                14 |
| Bravo   |    3 |                22 |
+---------+------+-------------------+

然后我想在商店一级计算这些不同产品的平均值 . 我接近这个的方法是通过先前的计算，并在其上运行SUMX并将其除以不同的周：

SUMX(
SUMMARIZE(Sales,[Store],[Week],"Distinct Products",DISTINCTCOUNT([Product]))
,[Distinct Products]
) / DISTINCTCOUNT([Week])

+---------+------------------+
|  Store  | Average Products |
+---------+------------------+
| Charlie | 11.3             |
| Bravo   | 18.7             |
+---------+------------------+

我将这个计算存储在一个度量中，当数据集较小时，它运行良好 . 但是现在数据集非常庞大，当我尝试使用该度量时，它会挂起，直到我必须取消该过程 .

有没有更有效的方法来做到这一点？

powerpivot powerbi dax

1 回答

  • 2

    SUMX在这种情况下是合适的，因为您希望为每个商店和每周独立计算不同的产品计数，然后按商店汇总，然后除以商店的周数 . 没有办法解决这个问题 . （如果有，我会推荐它 . ）

    但是，SUMX是一个迭代器，因此可能导致减速 . 由于我们无法完全消除SUMX，因此这里最大的因素是您拥有的商店/周的组合数量 .

    要确认商店/周的组合数量是否是减速的来源，请尝试过滤或从数据模型的副本中删除50％，看看是否加快了速度 . 如果没有超时，请重新添加更多内容，以了解有多少组合是失败点 .

    使用完整数据集使事情更快：

    • 在拖动度量之前，您可以在数据透视表中过滤到商店/周的子集 . 与首先拖动度量，然后添加过滤器相比，这通常会获得更快的结果 . （这不是对您的度量的真正改变，而是对模型用户的更多行为更改） .

    • 您可能希望考虑在比周（例如月）更高的级别进行分组，以减少必须迭代的组合数量

    • 如果您运行的是Excel 32位，或者只有4GB的RAM，请考虑使用64位Excel和/或更强大的机器（我怀疑是这种情况，但包括全面性 - Power Pivot可以是一种资源猪）

    • 如果您可以将模型移动到Power BI Desktop（我不相信Power Pivot支持计算表），您可以将SUMMARIZE提取到计算表中，然后重新编写度量以引用该计算表而不是 . 这减少了测量必须在运行时执行的计算次数，因为所有商店/周的组合加上不同的产品数量都将被预先计算（只留下您的度量的求和和除法 - 很多少工作） .

    .

    Calculated Table =
SUMMARIZE (
    Sales,
    [Store],
    [Week],
    "Distinct Products", DISTINCTCOUNT ( Sales[Product] )
)

    注意：上面计算的表格代码是基本的，主要是作为概念证明 . 如果这是您采用的路径，则'll want to make sure you have a separate store dimension to join the calculated table to, as this won' t直接加入源表

    Measure Using Calc Table =
SUMX (
    'Calculated Table',
    [Distinct Products] / DISTINCTCOUNT ( 'Calculated Table'[Week] )
)

    杰森托马斯在计算表上有一篇很棒的文章，他们什么时候可以在这里有用：http://sqljason.com/2015/09/my-thoughts-on-calculated-tables-in.html .

    如果您不能使用计算表，但您的数据来自某种形式的数据库，那么您可以在SQL中执行相同的逻辑，然后导入预先准备的独立存储/月份及其不同计数的单独表 .

    我希望其中一些证明是有用的（或者你已经用另一种方式解决了问题） .

    回复于

相关问题