Is SUMMARIZE better than EARLIER?

[masplin]

I'm about to embark on rebuilding a model that has been growing since 2011 and hopefully doing a better job of it with all I have learnt since that time, when I was making it up as a went along!

In my original model had had lots of calculations where i needed to calculate sub tables before performing some other function. In general they looked like:

CALCULATE(
COUNTROWS(Table),
FILTER(
Table,
Table[ABC]=EARLIER(Table[ABC]) &&
Table[XYZ]=EARLIER(Table[XYZ])
)
)

Would I be right thinking that i am much better to use SUMMARIZE to create any sub tables that then get looped through by another function?


CALCULATE(
COUNTROWS(Table),
SUMMARIZE(
Table,
Table[ABC],
Table[XYZ]
)
)

Just wan tot check i'm reading up on the right thing as not 100% clear about how to use SUMMARIZE effectively.

Thanks
Mike

 

[scottsen]

I would love to see a "real" example here to wrap my head around it -- cuz honestly the first measure doesn't even look VALID to me...

Oh wait! Those are calc columns I guess? (not measures)

 

[masplin]

Here's a typical example. I have a list of transactions where the vehicle reg num is repeated for each vehicle. I need to rank the visit in reverse date order. So yes this is a calculated column. There are 350,000 records and about 150,000 reg num ranging from 1 to 50 visits.

     =RANKX(          
                               FILTER(
                                           MatchInput,
                                           [Reg Num]=EARLIER([Reg Num])
                                           ),
                               [Inv Last Mod Date]+[Unique Ref]/10000000,,0
                              )

would SUMMARIZE(
Matchinput,
[Reg Num]
)

be better somewhere in here?

Thanks
Mike

 

[scottsen]

This should be low on your list of "things to worry about" The calc column is only going to be evaluated 1 time anyway (at refresh time), so it's not like perf matters a ton.

A better question is (typically) -- do I really WANT this calc column?

fwiw, I don't use SUMMARIZE very often.

 

[masplin]

Hi Scott

Yes I appreciate that when i first started I wrote lots of calculated columns to follow that i was doing and I need to be removing as many as possible. My understand is unless you need the calculated column as a row/column/filter in the pivot table you should try to not have them.

Thanks
Mike

 

[scottsen]

As a general rule... Yes. Though, that only applies to fact/data table really. My calendar table always has a zillion helper columns. And some calcs are rough (RANKX), so that... a calc column makes sense (at long as you don't have to slice it).