DAX Query - Part 2

  • gary.strange-sqlconsumer

    SSCommitted

    Points: 1800

    Comments posted to this topic are about the item DAX Query - Part 2

  • Misha_SQL

    SSCertifiable

    Points: 5388

    I am just starting to learn DAX. Reading my first book on the subject and this article is very timely and informative.

  • hdsturgill

    Grasshopper

    Points: 21

    I was wondering, during what version of SSAS was the Tabular model introduced?

  • tallan

    Valued Member

    Points: 65

    The tabular model was introduced with SQL Server 2012 Analysis Services.

  • tallan

    Valued Member

    Points: 65

    Gary, Thank you for the interesting articles on DAX query. I am looking forward to the next installment.

  • tyjulianchen

    Valued Member

    Points: 65

    Hi Gary,

    I applied the calculate () and ALL () function as follows but got the wrong Product Universe numbers. Could you please help me dig out what's the problem? By the way, after conducting format() function, the value turns out to be text instead of number in Pivot table, why?

    Group Country ProductName CalendarYear Sum`of`Sales Product`Universe

    Europe France All-Purpose Bike Stand 2003 1431 1431

    Europe France All-Purpose Bike Stand 2004 1590 1590

    Europe France AWC Logo Cap 2003 881.02 881.02

    Europe France AWC Logo Cap 2004 1249.61 1249.61

    Europe France Bike Wash - Dissolver 2003 151.05 151.05

    Thanks.

    define

    measure 'Sales'[Sum of Sales] = SUM('Sales'[SalesAmount] )

    measure 'Sales'[Product Universe] = calculate( 'Sales'[Sum of Sales], ALL( Products[ProductName] ) )

    evaluate(

    summarize(

    'Sales',

    'Territory'[Group],

    'Territory'[Country],

    'Products'[ProductName],

    'Calendar'[CalendarYear],

    "Sum of Sales", [Sum of Sales],

    "Product Universe",[Product Universe]

    )

    )

    order by

    'Territory'[Group]

    ,'Territory'[Country]

    ,'Products'[ProductName]

    ,'Calendar'[CalendarYear]

  • tyjulianchen

    Valued Member

    Points: 65

    Hi Gary,

    I applied the calculate () and ALL () function as follows but got the wrong Product Universe numbers. Could you please help me dig out what's the problem? By the way, after conducting format() function, the value turns out to be text instead of number in Pivot table, why?

    Group Country ProductName CalendarYear Sum`of`Sales :w00t:Product`Universe

    Europe France All-Purpose Bike Stand 2003 1431 1431

    Europe France All-Purpose Bike Stand 2004 1590 1590

    Europe France AWC Logo Cap 2003 881.02 881.02

    Europe France AWC Logo Cap 2004 1249.61 1249.61

    Europe France Bike Wash - Dissolver 2003 151.05 151.05

    Thanks.

    define

    measure 'Sales'[Sum of Sales] = SUM('Sales'[SalesAmount] )

    measure 'Sales'[Product Universe] = calculate( 'Sales'[Sum of Sales], ALL( Products[ProductName] ) )

    evaluate(

    summarize(

    'Sales',

    'Territory'[Group],

    'Territory'[Country],

    'Products'[ProductName],

    'Calendar'[CalendarYear],

    "Sum of Sales", [Sum of Sales],

    "Product Universe",[Product Universe]

    )

    )

    order by

    'Territory'[Group]

    ,'Territory'[Country]

    ,'Products'[ProductName]

    ,'Calendar'[CalendarYear]

Viewing 7 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply