none
Aggregate/Summarize on other level than queried

    Question

  • Hi,

    I need some help making one «special» Tabular DAX calculation spin quicker.
    I have a tabular model with a dimension Contract (ContractLine, Contract ++) and fact (Amount) plus several other dimensions to this fact table.
    I want to report for each Contract Line, both the Amount for this Line and the Contract total, so if one contract has 3 contract lines, the report shows all tree contract lines with its line amount and all with the same total contract amount. I’m using PerformancePoint, so using MDX for query is given.

    The DAX calculation:
    ContractLineAmount:= Sum([Amount])
    ContractAmount:=
      CALCULATE( [ContractLineAmount]
      ; Summarize(Contract; Contract[Contract])
    )

    Hurray! It works! But when including more dimensions it does not:

    SELECT  { [ContractLineAmount], [ContractAmount]} on 0,
    [Contract].[ContractLine] .[ContractLine].ALLMEMBERS *
    [Contract].[Attribute1]. [Attribute1].ALLMEMBERS*
    [Dim2].[Attribute2] .[Attribute2].ALLMEMBERS *
    [Dim3].[Attribute3] .[Attribute3].ALLMEMBERS
    ON 1
    From [Model]

    The contract lines can have totally different dimension values than the other contract lines in the same contract, so I tried to use ALL() in the rule to get the correct result:

    ContractAmount:=
      CALCULATE( [ContractLineAmount]
        ; ALLEXCEPT(Contract; Contract[Contract])
        ; ALL(Dim2)
        ; ALL(Dim3)
        …
        ; ALL(Dim10)
        ; Summarize(Contract; Contract[Contract])
    )

    This makes the MDX-result explode! (of course it does, I just told it to). So I’ve tried different things, like checking if there is a row for the actual contract line. This works, but is terribly slow with many dimensions.

    ContractAmount:=
     IF( COUNTROWS(Contract); 
      CALCULATE( [ContractLineAmount]
          ; ALLEXCEPT(Contract; Contract[Contract])
          ; ALL(Dim2)
          ; ALL(Dim3)
          …
          ; ALL(Dim10)
          ; Summarize(Contract; Contract[Contract])
      ); Blank()
    )

    Any Idea how this can be solved? AND make it spin quick with MDX?
    I tried replicating this in Adventureworks, but of course, there it was blazing fast, so I guess number of dimensions and members has an impact.

    • Edited by OleJakobK Friday, March 14, 2014 7:40 PM formatting
    Friday, March 14, 2014 7:37 PM

Answers

  • Hi Darren,

    That was actually slower than the summarize-approach.

    I did some more testing and found that making a calculated member in MDX to get up to the ALL-Level for my Dimensions made it much faster than giving the ALL-Level in the DAX-calculation:

    With Member Measures.[ContractAmount ] AS
       Measures.[HasContractRow] *
       (Measures.[ContractAmount]
        ,[Dim2].[Attribute2].[All]
     ,[Dim3].[Attribute3].[All]
     ..
     ,[Dim10].[Attribute10].[All]
     )

    Where the HasContractRow is defined as a DAX calculation as:
    HasContractRow:=if( ISEMPTY('Contracts'); BLANK(); 1)

    I Guess it makes better use of autoexists in this way?

    Monday, March 17, 2014 11:24 AM

All replies

  • I'm not sure if this would be quicker or not, but I think another way to achieve your result is the following:

    [Contract Amount]:=if ( countrows(contractline) > 0 , CALCULATE(sum([ContractLineAmount]),values(Contract[Contract]),All(Contract)), blank())


    http://darren.gosbell.com - please mark correct answers

    Sunday, March 16, 2014 4:56 AM
  • Hi Darren,

    That was actually slower than the summarize-approach.

    I did some more testing and found that making a calculated member in MDX to get up to the ALL-Level for my Dimensions made it much faster than giving the ALL-Level in the DAX-calculation:

    With Member Measures.[ContractAmount ] AS
       Measures.[HasContractRow] *
       (Measures.[ContractAmount]
        ,[Dim2].[Attribute2].[All]
     ,[Dim3].[Attribute3].[All]
     ..
     ,[Dim10].[Attribute10].[All]
     )

    Where the HasContractRow is defined as a DAX calculation as:
    HasContractRow:=if( ISEMPTY('Contracts'); BLANK(); 1)

    I Guess it makes better use of autoexists in this way?

    Monday, March 17, 2014 11:24 AM