Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
9 years ago
Solved

DAX HELP Multi Sumx alternative?

Can someone take a look at the measure below and tell me if there is a better way to accomplish what I am attemping with a multi-level "SUMX".? Basically trying to get a feel for if there is an optim...
Need Help
  • OwenAuger's avatar
    OwenAuger
    9 years ago

    Anonymous

     

    Thanks for that :)

     

    You will have to change the measure if there are multiple fact tables involved.

     

    I have two ideas:

     

    1. Use SUMX with SUMMARIZECOLUMNS. SUMMARIZECOLUMNS doesn't require you to specify the table to be summarized, and if you add a column with an expression, it will automatically remove rows where the expression is blank.
      (Side note: SUMMARIZECOLUMNS didn't previously work within a filter context, but now it appears to work. Interesting article here)
      PnL SUMMARIZECOLUMNS =
SUMX (
    SUMMARIZECOLUMNS (
        'CALENDAR'[DATE],
        HE[HE],
        CTRL[CTRL],
        DataClass[ZONE],
        "ExpressionToSum",
        ( [INCS MWH] + [DECS MWH (neg)] ) * [DART Spread]
            + [DEV Cost INCS]
            + [DEV Cost DECS]
    ),
    [ExpressionToSum]
)
    2. Use the SUMMARIZE method with multiple fact tables by SUMMARIZE-ing each table and take the union.
      PnL SUMMARIZE Union =
VAR BIDS_Summarized =
    SUMMARIZE ( BIDS, 'CALENDAR'[DATE], HE[HE], CTRL[CTRL], DataClass[ZONE] )
VAR LMP_Summarized =
    SUMMARIZE ( LMP, 'CALENDAR'[DATE], HE[HE], CTRL[CTRL], DataClass[ZONE] )
VAR DEV_Summarized =
    SUMMARIZE ( LMP, 'CALENDAR'[DATE], HE[HE], CTRL[CTRL], DataClass[ZONE] )
VAR Union_Summarized =
    DISTINCT ( UNION ( BIDS_Summarized, LMP_Summarized, DEV_Summarized ) )
RETURN
    SUMX (
        Union_Summarized,
        ( [INCS MWH] + [DECS MWH (neg)] )
            * [DART Spread]
            + [DEV Cost INCS]
            + [DEV Cost DECS]
    )

     

    I think the SUMMARIZECOLUMNS version should perform best, but would be interested in how actual performance turns out.

     

    Cheers,

    Owen :)

OwenAuger's avatar
OwenAuger
Super User
9 years ago

Hi AlfonsoAnonymous, Anonymous, Anonymous

 

A pattern I have used in the past instead of nested SUMX is the SUMX ( SUMMARIZE ( ... ), ... ) pattern. I have found it performs better in some models. The key difference from nested SUMXs is that it will ensure you sum over only existing combinations of the dimensions.

 

This pattern works as long as the dimensions which you are SUMX-ing over are all on the 1-side of a relationship with your fact table.

 

Also, since your two quad-SUMXs were over the same dimensions, you can get by with one SUMX

 

The result would be something like this:

 

 

PnL =
SUMX (
   SUMMARIZE ( YourFactTable, 'CALENDAR'[DATE], HE[HE], CTRL[CTRL], DataClass[ZONE] ),
   [INCS MWH] * [DART Spread] + [DEV Cost INCS]
   + [DECS MWH (neg)] * [DART Spread] + [DEV Cost DECS]
)

Or, since [DART Spread] appears in both expressions being summed, you could simplify slightly (to avoid evaluating it twice):

 

 

PnL =
SUMX (
   SUMMARIZE ( YourFactTable, 'CALENDAR'[DATE], HE[HE], CTRL[CTRL], DataClass[ZONE] ),
   ( [INCS MWH] + [DECS MWH (neg)] ) * [DART Spread]
   + [DEV Cost INCS] + [DEV Cost DECS]
)

 

Would be interested in whether these perform any better.

 

Cheers,

Owen :)

Anonymous's avatar
Anonymous
Not applicable
9 years ago

OwenAuger thanks for taking the time! I'm eager to try this out today. I've never used SUMMERIZE before. 

 

One wrinkle- does it matter that the compnents to my measures are on 3 different fact tables? They are on the 1-side (I have a FACT tbl for MWH measure, FACT tbl for DART, and FACT tbl for DEV). 

 

Check out the diagram view that I've annotated for better context- 

 

 

 

THNX AGAIN, 

 

fonz

  • OwenAuger's avatar
    OwenAuger
    Super User
    9 years ago

    Anonymous

     

    Thanks for that :)

     

    You will have to change the measure if there are multiple fact tables involved.

     

    I have two ideas:

     

    1. Use SUMX with SUMMARIZECOLUMNS. SUMMARIZECOLUMNS doesn't require you to specify the table to be summarized, and if you add a column with an expression, it will automatically remove rows where the expression is blank.
      (Side note: SUMMARIZECOLUMNS didn't previously work within a filter context, but now it appears to work. Interesting article here)
      PnL SUMMARIZECOLUMNS =
SUMX (
    SUMMARIZECOLUMNS (
        'CALENDAR'[DATE],
        HE[HE],
        CTRL[CTRL],
        DataClass[ZONE],
        "ExpressionToSum",
        ( [INCS MWH] + [DECS MWH (neg)] ) * [DART Spread]
            + [DEV Cost INCS]
            + [DEV Cost DECS]
    ),
    [ExpressionToSum]
)
    2. Use the SUMMARIZE method with multiple fact tables by SUMMARIZE-ing each table and take the union.
      PnL SUMMARIZE Union =
VAR BIDS_Summarized =
    SUMMARIZE ( BIDS, 'CALENDAR'[DATE], HE[HE], CTRL[CTRL], DataClass[ZONE] )
VAR LMP_Summarized =
    SUMMARIZE ( LMP, 'CALENDAR'[DATE], HE[HE], CTRL[CTRL], DataClass[ZONE] )
VAR DEV_Summarized =
    SUMMARIZE ( LMP, 'CALENDAR'[DATE], HE[HE], CTRL[CTRL], DataClass[ZONE] )
VAR Union_Summarized =
    DISTINCT ( UNION ( BIDS_Summarized, LMP_Summarized, DEV_Summarized ) )
RETURN
    SUMX (
        Union_Summarized,
        ( [INCS MWH] + [DECS MWH (neg)] )
            * [DART Spread]
            + [DEV Cost INCS]
            + [DEV Cost DECS]
    )

     

    I think the SUMMARIZECOLUMNS version should perform best, but would be interested in how actual performance turns out.

     

    Cheers,

    Owen :)