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

[tecumseh]

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]

)

[tecumseh]

@v-tsaipranay 

Solved by response from @tayloramy 

Thanks,
w

[v-tsaipranay]

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.

[tecumseh]

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 · Accepted solution]

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.

[tecumseh]

thanks @tayloramy,

that solved it

Thanks,

w

[tayloramy]

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.

[SwarnaTeja]

@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]

Thanks @SwarnaTeja ,

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

thanks,

w