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 troublesh...
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.

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.