Home Dashboard Directory Help
Search

non-visual totals not working in DAXMD preview by GregGalloway


Status: 

Closed
 as By Design Help for as By Design


2
0
Sign in
to vote
Type: Bug
ID: 776006
Opened: 1/7/2013 8:08:10 PM
Access Restriction: Public
0
Workaround(s)
view
0
User(s) can reproduce this bug

Description

Deploy the attached database. Connect under a user in the US role. Note the role has dimension data security so the user can only see details about US countries/cities/PostalCodes. But the "visual totals" checkbox isn't checked so the grand total should still be the real grand total.

MDX queries under users in that role do properly show the real grand total: 60,398

select [Measures].[Order Quantity] on 0,
[Geography].[By State].[State].Members + [Geography].[By State].[All] on 1
from [Adventure Works]

However the DAX queries generated by Power View don't property show the real grand total:

EVALUATE
CALCULATETABLE(
    ROW(
     "Order_Quantity", 'Internet Sales'[Order Quantity]
    ),
    KEEPFILTERS(
     FILTER(
        KEEPFILTERS(VALUES('Geography'[State 2.UniqueName])),
        NOT(ISBLANK('Internet Sales'[Order Quantity]))
     )
    )
)


EVALUATE
ADDCOLUMNS(
    KEEPFILTERS(
     FILTER(
        KEEPFILTERS(
         SUMMARIZE(VALUES('Geography'), 'Geography'[State 2.UniqueName], 'Geography'[State 2])
        ),
        NOT(ISBLANK('Internet Sales'[Order Quantity]))
     )
    ),
    "Order_Quantity", 'Internet Sales'[Order Quantity]
)

ORDER BY
'Geography'[State 2], 'Geography'[State 2.UniqueName]
Details
Sign in to post a comment.
Posted by GregGalloway on 2/11/2013 at 7:07 AM
Thanks Siva. That makes some sense from what I understand of Tabular and Power View. Please do make sure to call out this limitation in the DAXMD documentation going forward though. In terms of dimension data security, non-visual totals are recommended for performance reasons, so this is not an uncommon configuration.
Posted by Microsoft on 2/10/2013 at 9:39 PM
Hello,

Thank you for the feedback. All the DAX queries generated by Power View include visual totals. Hence you see the visual totals and not the grand total. This is the current behavior of Power View and hence resolving this as By design.

Regards
Siva
Sign in to post a workaround.
File Name Submitted By Submitted On File Size  
DAXMD Test.zip 1/7/2013 33 KB