Power BI报表DAX查询嵌套差异及加载缓慢问题排查

我负责在Power BI Desktop中重写一份报表，可视化对象是将多列拖入Values容器的表格。已经逐字段复刻表格，但对比原报表和我构建的报表DAX查询，二者结构差异明显，我的版本加载速度慢很多，想知道为什么我的查询存在更多嵌套。

原报表DAX查询 #

// DAX Query
DEFINE
  VAR __DS0FilterTable = 
    FILTER(
      KEEPFILTERS(VALUES('applicant'[Client Name])),
      NOT('applicant'[Client Name] IN {"TEST TEST"})
    )

  VAR __DS0Core = 
    SUMMARIZECOLUMNS(
      ROLLUPADDISSUBTOTAL(
        ROLLUPGROUP(
          'applicant'[Id],
          'applicant'[Client Name],
          'applicant'[Assistance Requested Date],
          'applicant'[physicalZip],
          'applicant'[physicalAddress],
          'PhysicalAddress Lookup'[physCity],
          'assistance'[funding-source],
          'assistance'[app-assistance],
          'assistance'[assistance-type],
          'assistance'[disposition],
          'assistance'[fin-WD-date],
          'assistance'[fin-check],
          'assistance'[fin-date-paid],
          'assistance'[fin-Invoice],
          'applicant'[AMICategory],
          'assistance'[Start of coverage period]
        ), "IsGrandTotalRowTotal"
      ),
      __DS0FilterTable,
      "Sumamount_owed", CALCULATE(SUM('assistance'[amount-owed])),
      "Sumamount_paid", CALCULATE(SUM('assistance'[amount-paid])),
      "Sumnumberinhousehold", CALCULATE(SUM('applicant'[numberinhousehold])),
      "SumTotalMonthlyIncome", CALCULATE(SUM('applicant'[TotalMonthlyIncome]))
    )

  VAR __DS0PrimaryWindowed = 
    TOPN(
      502,
      __DS0Core,
      [IsGrandTotalRowTotal],
      0,
      'assistance'[fin-date-paid],
      0,
      'applicant'[Id],
      1,
      'applicant'[Client Name],
      1,
      'applicant'[Assistance Requested Date],
      1,
      'applicant'[physicalZip],
      1,
      'applicant'[physicalAddress],
      1,
      'PhysicalAddress Lookup'[physCity],
      1,
      'assistance'[funding-source],
      1,
      'assistance'[app-assistance],
      1,
      'assistance'[assistance-type],
      1,
      'assistance'[disposition],
      1,
      'assistance'[fin-WD-date],
      1,
      'assistance'[fin-check],
      1,
      'assistance'[fin-Invoice],
      1,
      'applicant'[AMICategory],
      1,
      'assistance'[Start of coverage period],
      1
    )

EVALUATE
  __DS0PrimaryWindowed

ORDER BY
  [IsGrandTotalRowTotal] DESC,
  'assistance'[fin-date-paid] DESC,
  'applicant'[Id],
  'applicant'[Client Name],
  'applicant'[Assistance Requested Date],
  'applicant'[physicalZip],
  'applicant'[physicalAddress],
  'PhysicalAddress Lookup'[physCity],
  'assistance'[funding-source],
  'assistance'[app-assistance],
  'assistance'[assistance-type],
  'assistance'[disposition],
  'assistance'[fin-WD-date],
  'assistance'[fin-check],
  'assistance'[fin-Invoice],
  'applicant'[AMICategory],
  'assistance'[Start of coverage period]

我的报表DAX查询 #

// DAX Query
DEFINE
  VAR __DS0Core = 
    SELECTCOLUMNS(
      KEEPFILTERS(
        FILTER(
          KEEPFILTERS(
            SUMMARIZECOLUMNS(
              ROLLUPADDISSUBTOTAL(
                ROLLUPGROUP(
                  'era-cdbg Applicant'[ApplicantId],
                  'era-cdbg Applicant'[FullName],
                  'era-cdbg Application'[ApplicationDate],
                  'era-cdbg Application'[ApplicantPhysicalZip],
                  'era-cdbg Application'[ApplicantPhysicalAddress1],
                  'era-cdbg Application'[ApplicantPhysicalCity],
                  'era-cdbg Assistance'[FundingSource],
                  'era-cdbg Assistance'[Category],
                  'era-cdbg Assistance'[Disposition],
                  'era-cdbg Assistance'[PaymentSubmittedWD],
                  'era-cdbg Assistance'[CheckNumber],
                  'era-cdbg Assistance'[DatePaid],
                  'era-cdbg Assistance'[FinInvoice],
                  'era-cdbg Application'[AMICategory],
                  'era-cdbg Assistance'[Start of coverage period]
                ), "IsGrandTotalRowTotal"
              ),
              "SumAmountOwed", CALCULATE(SUM('era-cdbg Assistance'[AmountOwed])),
              "SumAmountPaid", CALCULATE(SUM('era-cdbg Assistance'[AmountPaid])),
              "Sumhhm", CALCULATE(SUM('era-cdbg Application'[hhm])),
              "HasDataera_cdbg_Assistance", IGNORE(
                CALCULATE(NOT(ISEMPTY('era-cdbg Assistance')))
              )
            )
          ),
          OR(
            OR(
              NOT(ISBLANK([SumAmountOwed])),
              NOT(ISBLANK([SumAmountPaid]))
            ),
            [HasDataera_cdbg_Assistance]
          )
        )
      ),
      "'era-cdbg Applicant'[ApplicantId]", 'era-cdbg Applicant'[ApplicantId],
      "'era-cdbg Applicant'[FullName]", 'era-cdbg Applicant'[FullName],
      "'era-cdbg Application'[ApplicationDate]", 'era-cdbg Application'[ApplicationDate],
      "'era-cdbg Application'[ApplicantPhysicalZip]", 'era-cdbg Application'[ApplicantPhysicalZip],
      "'era-cdbg Application'[ApplicantPhysicalAddress1]", 'era-cdbg Application'[ApplicantPhysicalAddress1],
      "'era-cdbg Application'[ApplicantPhysicalCity]", 'era-cdbg Application'[ApplicantPhysicalCity],
      "'era-cdbg Assistance'[FundingSource]", 'era-cdbg Assistance'[FundingSource],
      "'era-cdbg Assistance'[Category]", 'era-cdbg Assistance'[Category],
      "'era-cdbg Assistance'[Disposition]", 'era-cdbg Assistance'[Disposition],
      "'era-cdbg Assistance'[PaymentSubmittedWD]", 'era-cdbg Assistance'[PaymentSubmittedWD],
      "'era-cdbg Assistance'[CheckNumber]", 'era-cdbg Assistance'[CheckNumber],
      "'era-cdbg Assistance'[DatePaid]", 'era-cdbg Assistance'[DatePaid],
      "'era-cdbg Assistance'[FinInvoice]", 'era-cdbg Assistance'[FinInvoice],
      "'era-cdbg Application'[AMICategory]", 'era-cdbg Application'[AMICategory],
      "'era-cdbg Assistance'[Start of coverage period]", 'era-cdbg Assistance'[Start of coverage period],
      "IsGrandTotalRowTotal", [IsGrandTotalRowTotal],
      "SumAmountOwed", [SumAmountOwed],
      "SumAmountPaid", [SumAmountPaid],
      "Sumhhm", [Sumhhm]
    )

  VAR __DS0PrimaryWindowed = 
    TOPN(
      502,
      __DS0Core,
      [IsGrandTotalRowTotal],
      0,
      'era-cdbg Applicant'[ApplicantId],
      1,
      'era-cdbg Applicant'[FullName],
      1,
      'era-cdbg Application'[ApplicationDate],
      1,
      'era-cdbg Application'[ApplicantPhysicalZip],
      1,
      'era-cdbg Application'[ApplicantPhysicalAddress1],
      1,
      'era-cdbg Application'[ApplicantPhysicalCity],
      1,
      'era-cdbg Assistance'[FundingSource],
      1,
      'era-cdbg Assistance'[Category],
      1,
      'era-cdbg Assistance'[Disposition],
      1,
      'era-cdbg Assistance'[PaymentSubmittedWD],
      1,
      'era-cdbg Assistance'[CheckNumber],
      1,
      'era-cdbg Assistance'[DatePaid],
      1,
      'era-cdbg Assistance'[FinInvoice],
      1,
      'era-cdbg Application'[AMICategory],
      1,
      'era-cdbg Assistance'[Start of coverage period],
      1
    )

EVALUATE
  __DS0PrimaryWindowed

ORDER BY
  [IsGrandTotalRowTotal] DESC,
  'era-cdbg Applicant'[ApplicantId],
  'era-cdbg Applicant'[FullName],
  'era-cdbg Application'[ApplicationDate],
  'era-cdbg Application'[ApplicantPhysicalZip],
  'era-cdbg Application'[ApplicantPhysicalAddress1],
  'era-cdbg Application'[ApplicantPhysicalCity],
  'era-cdbg Assistance'[FundingSource],
  'era-cdbg Assistance'[Category],
  'era-cdbg Assistance'[Disposition],
  'era-cdbg Assistance'[PaymentSubmittedWD],
  'era-cdbg Assistance'[CheckNumber],
  'era-cdbg Assistance'[DatePaid],
  'era-cdbg Assistance'[FinInvoice],
  'era-cdbg Application'[AMICategory],
  'era-cdbg Assistance'[Start of coverage period]

原因分析 #

• 自动添加的空行过滤逻辑：你的查询里多了两层嵌套的FILTER和外层的SELECTCOLUMNS，这是因为Power BI自动生成了过滤空行的逻辑——检查SumAmountOwed、SumAmountPaid非空，或者era-cdbg Assistance表有数据。而原查询没有这层过滤，直接用SUMMARIZECOLUMNS加单独的筛选表处理数据，结构更简洁。
• 冗余的列映射：你的查询用SELECTCOLUMNS重新映射了所有字段，这会额外增加数据处理的步骤，原查询直接通过SUMMARIZECOLUMNS输出结果，没有这层不必要的嵌套。
• 数据模型或可视化设置差异：可能你的数据模型中表关系和原模型不同，Power BI为了避免出现无效空行，自动生成了更复杂的嵌套逻辑；另外，你可能开启了可视化的“隐藏空行”选项，原报表没有开启，这也是导致查询嵌套增多的原因之一。

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