Forum Discussion

tecumseh's avatar
tecumseh
Resolver III
10 months ago
Solved

DQV In Power BI Service does not return Same Count or Total Amount as Manual Filter Table in PBIX

Hi,

I created a query in Power BI Service returns 207 rows total : $239.0K
But when I manually filter the data in the pbix I get 216 rows total : $247.7K

What are some issues I might troubleshoot to find what is causing the difference?

Thanks,

w

 

DEFINE

VAR _tbl = 

FILTER(

    Bookings,

    Bookings[Advertiser] = "xcustomer" &&

    Bookings[Date] = DATE(2025, 08, 01)

)

EVALUATE

SUMMARIZE(

    _tbl,

    [Date],

    [Advertiser],

    [Order #],

    [Net Revenue]

)
Need Help
  • tayloramy's avatar
    tayloramy
    10 months ago

    Hi tecumseh,

     

    Great follow-up. The error comes from how SUMMARIZECOLUMNS expects its filter arguments: each filter must be a table expression, not a Boolean expression. In other words, you can’t put Bookings[Date] = DATE(...) directly inside SUMMARIZECOLUMNS. Wrap your predicates in a table expression like FILTER(ALL(Bookings), ...), and place that table as its own argument.

     

    EVALUATE
SUMMARIZECOLUMNS(
    Bookings[Advertiser],
    Bookings[Order #],
    Bookings[Date],

    -- filter tables must be table expressions (NOT booleans):
    FILTER(
        ALL(Bookings),
        Bookings[Advertiser] = "xCustomer"
            && Bookings[Date] >= DATE(2025,8,1)
            && Bookings[Date] <  DATE(2025,8,2)  -- half-open day range
    ),

    -- prefer your measure for totals if you have one:
    "Total Revenue", [Net Revenue]  -- or CALCULATE(SUM(Bookings[Net Revenue]))
)

    If you found this helpful, consider giving some Kudos. If I answered your question or solved your problem, mark this post as the solution.

8 Replies

  • SwarnaTeja's avatar
    SwarnaTeja
    Resolver I
    10 months ago

    tecumseh , Please check if you have selected don't summarize when you filter manually in pbix. Usually if there are more rows with same details then the Revenue might get summed up in the dax.

    • tecumseh's avatar
      tecumseh
      Resolver III
      10 months ago

      Thanks SwarnaTeja ,

      That could account for difference in row counts, but what about the summary total variance?

      thanks,

      w

  • tayloramy's avatar
    tayloramy
    Super User
    10 months ago

    Hi tecumseh,

     

    Here are the most common reasons a DAX query (or DAX Query View) in the Service won’t match a “manually filtered” table in Desktop, plus quick fixes you can try right away.

     

    1. Date = DATE(2025,8,1) is too strict for DateTime columns
      If Bookings[Date] has a time component, = will exclude anything not exactly 12:00 AM.
      Fix: filter as a day range:
      Bookings[Date] >= DATE(2025,8,1)
&& Bookings[Date] <  DATE(2025,8,2)
    2. SUMMARIZE is grouping by Net Revenue
      Your query:
      EVALUATE
SUMMARIZE(
  _tbl,
  [Date],
  [Advertiser],
  [Order #],
  [Net Revenue]   -- this groups by the revenue value itself
)
      Including [Net Revenue] inside SUMMARIZE turns it into a grouping column, which changes both the row count and how totals roll up.
      Fix A (detail-like list + measure):
      -- assume [Net Revenue] is a measure
EVALUATE
ADDCOLUMNS(
  _tbl,
  "Net Revenue", [Net Revenue]
)
      Fix B (proper grouping + measure):
      EVALUATE
SUMMARIZECOLUMNS(
  Bookings[Date],
  Bookings[Advertiser],
  Bookings[Order #],
  "Net Revenue", [Net Revenue]
)
      (Reference: SUMMARIZE vs SUMMARIZECOLUMNS.)
    3. RLS/OLS differences between Desktop and Service
      If roles apply in the Service, your DAX query will return fewer rows than Desktop unless you “View as Role.”
      Check: In Desktop, use “View as” to mirror the role, or run the DAX against the same role context.
      (Reference: Row-level security.)

     

    If you found this helpful, consider giving some Kudos. If I answered your question or solved your problem, mark this post as the solution.

  • tecumseh's avatar
    tecumseh
    Resolver III
    10 months ago

    Thanks tayloramy 

    1 & 2 returned the same as OP so no help there.
    I'm trying SUMMARIZECOLUMNS, but I'm getting an error in the FILTER clause.
    Thoughts on what I'm doing incorrectly?

    Thanks,
    w

    EVALUATE
    SUMMARIZECOLUMNS(
    Bookings[Advertiser],
    Bookings[Order #],
    Bookings[Date],
    "Total Revenue", SUM(Bookings[Net Revenue]),
    FILTER(
    Bookings[Date] = DATE(2025,08,01) &&
    Bookings[Advertiser] = "xCustomer"
    )
    )M  

     

    • tayloramy's avatar
      tayloramy
      Super User
      10 months ago

      Hi tecumseh,

       

      Great follow-up. The error comes from how SUMMARIZECOLUMNS expects its filter arguments: each filter must be a table expression, not a Boolean expression. In other words, you can’t put Bookings[Date] = DATE(...) directly inside SUMMARIZECOLUMNS. Wrap your predicates in a table expression like FILTER(ALL(Bookings), ...), and place that table as its own argument.

       

      EVALUATE
SUMMARIZECOLUMNS(
    Bookings[Advertiser],
    Bookings[Order #],
    Bookings[Date],

    -- filter tables must be table expressions (NOT booleans):
    FILTER(
        ALL(Bookings),
        Bookings[Advertiser] = "xCustomer"
            && Bookings[Date] >= DATE(2025,8,1)
            && Bookings[Date] <  DATE(2025,8,2)  -- half-open day range
    ),

    -- prefer your measure for totals if you have one:
    "Total Revenue", [Net Revenue]  -- or CALCULATE(SUM(Bookings[Net Revenue]))
)

      If you found this helpful, consider giving some Kudos. If I answered your question or solved your problem, mark this post as the solution.

  • Anonymous's avatar
    Anonymous
    Not applicable
    10 months ago

    Hi tecumseh ,

    Thanks for reaching out to the Microsoft fabric community forum.

     

    Could you please let us know if the issue has been resolved? I wanted to check if you had the opportunity to review the information provided by tayloramy  . If you still require support, please let us know, we are happy to assist you.

     

    Thank you.