具有日期范围的DAX计算执行不好

Question

我有一个DAX公式，它的表现非常糟糕，希望这里有人能提出解决方案。

我有一个包含大约400000行数据的表。ProductID(示例字段(、startdate、enddate和IsActive标志字段。该表中的数据应通过多种方式进行报告。在某些报告中，我希望看到选定时间段内的所有活动产品，而在其他报告中，只希望看到当月最后一天活动的产品数量。

因此，我创建了两个DAX查询来计算这一点。

首先我计算活性产物：

_Calc_Count Fields :=
CALCULATE (
DISTINCTCOUNT ( MyFactTable[ProductID] ),
FILTER (
MyFactTable,
MyFactTable[StartDate] <= CALCULATE ( MAX ( 'Date'[Date] ) )
&& MyFactTable[EndDate] >= CALCULATE ( MIN ( 'Date'[Date] ) )
),
MyFactTable[IsActive] = 1
)

请注意，使用此计算的报告也可以包含日期范围(甚至可以选择全年(或多年(，并在筛选器中选择开始日期和结束日期(。该报告还对客户端组等其他筛选器进行了切片。

然后我有第二个计算，它使用第一个并应用LASTNONBLANK函数：

Last Non Blank Value :=
CALCULATE (
[_Calc_Count Fields],
LASTNONBLANK ( 'Date'[Date], [_Calc_Count Fields] )
)

这两种计算都非常缓慢。

有人能提出更好的方法吗？DAX公式可以优化吗？还是应该完全重写？

ps。我正在使用Analysis Services表格模型。

提前感谢大家的回复！

Answer 1

优化需要考虑很多方面。

首先，你需要了解瓶颈在哪里
我会做三个单独的初步测试：
A(用简单的计数更改DISTINCTCOUNT
B(移除FILTER
C(移除IsActive

然后你就可以知道在哪里优先考虑你的工作，但是有一些非常简单的通用优化你无论如何都可以做：

1.利用变量，因此公式变为：

_Calc_Count Fields 3:=
VAR _startdate = CALCULATE ( MAX ( 'Date'[Date] ) )
VAR _enddate = CALCULATE ( MIN ( 'Date'[Date] ) )
RETURN
CALCULATE (
DISTINCTCOUNT ( MyFactTable[ProductID] ),
FILTER (
MyFactTable,
MyFactTable[StartDate] <= _startdate 
&& MyFactTable[EndDate] >= _enddate
),
MyFactTable[IsActive] = 1
)

2.如果您使用整个事实表作为FILTER的第一个参数，存储引擎将在内存中加载扩展表，这是非常昂贵的。因此，作为第二步，公式应该变成：

_Calc_Count Fields 2:=
VAR _startdate = CALCULATE ( MAX ( 'Date'[Date] ) )
VAR _enddate = CALCULATE ( MIN ( 'Date'[Date] ) )
RETURN
CALCULATE (
DISTINCTCOUNT ( MyFactTable[ProductID] ),
MyFactTable[StartDate] <= _startdate && MyFactTable[EndDate] >= _enddate,
MyFactTable[IsActive] = 1
)

接下来，根据初步测试，您可以决定将精力投入到哪里
问题是DISTINCTCOUNT：
-探索一些近似DISTINTCOUNT(HIGH EFORT(的替代算法
-尝试在数据源(后端(中按ProductId对表进行排序，以便在AAS中进行更好的压缩
–确保ProductId是带编码提示的Integer data类型：Value

问题出现在FILTER中：
-尝试将"&&"更改为"，"(低工作量(
-调查StartDate和EndDate的基数。如果它们是DateTime，请删除"时间"部分。(LOW EFORT(
-尝试更改后端的数据源并按有用字段排序(例如，StartDate asc，这样AAS读取表时可能会执行更好的压缩(LOW EFFORT(
-确保StartDate和Date是整数数据类型，并带有编码提示：值(LOW AFORT(