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 :)

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
Icon for Super User rankSuper 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 :)