DAX HELP Multi Sumx alternative?

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