如何在DAX查询中通过Excel文件筛选指定ProductID？

我们日常工作使用多表结构的SSAS Tabular模型，通过Power BI导入模式用DAX查询连接该模型。现在需要分析其他用户存在公司SharePoint里的Excel文件中的指定ID列表——零食类数据有1亿行，但我们只需要处理这个每日动态更新、最多可达4000个的ID列表，希望能在DAX查询阶段直接筛选，避免先处理全量数据拖慢效率。

现有基础查询 #

Evaluate(
    Summarize(
          'Sales',
          'ProductTable'[ProductID],
          'ProductTable'[ProductName],
          'DimProductCategory'[CategoryName],
          'DimDate'[CalendarDate],
          "TotalSales", sumx(filter('Sales', 'DimProductCategory'[Category]="Snacks"), [SalesTotal])
       )
)

少量ID的临时处理方式 #

如果ID数量不多，可以用变量+TREATAS函数实现精准筛选，示例如下：

Define
Var __filterIDS=
          treatas(
               {1,2,3,4,5,6,7,8,9,10}, 'ProductTable'[ProductID])

Var __Query =
Summarize(
          'Sales',
          'ProductTable'[ProductID],
          'ProductTable'[ProductName],
          'DimProductCategory'[CategoryName],
          'DimDate'[CalendarDate],
          __filterIDS,
          "TotalSales", sumx(filter('Sales', 'DimProductCategory'[Category]="Snacks"), [SalesTotal])
       )

Evaluate
__Query

核心需求与当前痛点 #

但ID列表是动态变化的，数量最多能到4000个，我们想直接在DAX查询里读取Excel中的ID列表，理想写法类似：

Var __filterIDS=
          treatas(
               {list from excel}, 'ProductTable'[ProductID])

目前我们的做法是把Excel导入Power BI后合并ID列表再筛选，但这样必须先处理1亿条全量数据，效率极低，急需更高效的方案。

可行解决方案 #

方法1：用Power BI DirectQuery连接Excel（轻量临时方案） #

如果SharePoint上的Excel文件支持DirectQuery连接（需文件存于云存储且配置正确），可以在Power BI中建立DirectQuery连接到该Excel的ID列表表，然后直接在DAX中引用这个表做筛选：

Define
Var __filterIDS = TREATAS(SELECTCOLUMNS('Excel_ID_List', "ID", [ProductID]), 'ProductTable'[ProductID])

Var __Query =
SUMMARIZECOLUMNS(
          'ProductTable'[ProductID],
          'ProductTable'[ProductName],
          'DimProductCategory'[CategoryName],
          'DimDate'[CalendarDate],
          __filterIDS,
          "TotalSales", CALCULATE(SUM('Sales'[SalesTotal]), 'DimProductCategory'[Category]="Snacks")
       )

Evaluate
__Query

注：这里用SUMMARIZECOLUMNS替代SUMMARIZE，它会先应用筛选再聚合，大幅减少数据处理量。

方法2：Power Automate自动生成DAX变量（动态更新场景适配） #

用Power Automate读取SharePoint上的Excel ID列表，把ID拼接成DAX数组格式（比如{1,2,3,...}），然后自动更新Power BI中的DAX查询或度量值。这种方法不用导入Excel数据，直接把动态ID列表注入DAX查询，确保查询时只处理目标ID的数据。

方法3：在SSAS模型中添加外部ID表（长期推荐方案） #

如果有权限，可以在SSAS Tabular模型中直接添加对SharePoint Excel文件的连接（通过Get Data从SharePoint获取ID列表），将其作为模型内的一张表。之后在DAX查询中直接引用这张表做筛选，SSAS会在查询阶段自动处理关联，避免全量数据扫描：

Define
Var __filterIDS = TREATAS('SSAS_Excel_ID_List'[ProductID], 'ProductTable'[ProductID])

Var __Query =
SUMMARIZECOLUMNS(
          'ProductTable'[ProductID],
          'ProductTable'[ProductName],
          'DimProductCategory'[CategoryName],
          'DimDate'[CalendarDate],
          __filterIDS,
          "TotalSales", CALCULATE(SUM('Sales'[SalesTotal]), 'DimProductCategory'[Category]="Snacks")
       )

Evaluate
__Query

内容的提问来源于stack exchange，提问作者Fugazzetta