Trying to replace Summarize() with SummarizeColumns - AddMissingItems() Error + advanced ALL() issue

[charleshale]

I have ~12m row table all articles ever written on a large publishing website since 2010.   Let's simplify the table a bit and say it has the following data: 

Table: all_articles_ever

ArticleID	AuthorID	RoleID	Date
00001_I_love_powerBI	121	4	12/13/2019
00002_I_learning_powerBI_is_fun	121	4	12/13/2019

I am interested in counting the # of our staff who write 2 or more articles a day on average (to help cut down noise in our data by excluding folks like me who post only occasionally).   I have been using Summarize() and it works okay.  The 2 issues I have with it are (1) huge performance hit, and (2) having trouble using ALL() with it to return only the grand totals.   Here's what works okay:

TRY1#Staff>2DailyArticles = COUNTROWS(
    FILTER(
        SUMMARIZE(
           VALUES(all_articles_ever[UserID]),
           all_articles_ever[User_ID],
           "ABCD",CALCULATE (____//measure_for_daily_articles_count//__________)
            )
      ,[ABCD]>=2)
)

So, I tried the following and it works much faster to return a data table (if I go to Modelling/Add Table) but throws an error in Reports of AddMissingItems().

TRY2_#Staff>2DaiyArticles = COUNTROWS(
    FILTER(
    SUMMARIZECOLUMNS(
        all_articles_ever[User_ID],
        "ABCD", CALCULATE(____________//measure_daily_articles_count//___________)
        ),
    [ABCD]>2)
)

 QUESTION 1:

I have been reading SQLBI  and the master @marcorusso but am not following why TRY2 throws the AddMissingItems() error.

QUESTION 2: 

Let's say I capitulate and just take the performance hit using Summarize(), what's the best way to edit TRY1 to return only grand totals in Reports?

I can do this in a data table by Modelling/AddTable...

allart_Table = 
SUMMARIZE(
    FILTER(
        SUMMARIZE(
            FILTER(all_articles_ever, all_articles_ever[RoleID] = 4 || all_articles_ever[RoleID] = 5),
            all_articles_ever[UserID],
            DimDate[Year],
            "ABCD",CALCULATE([__DailyLocAvg_Art])    //This is a measure that divides # articles by # days
            )
   , [ABCD] >=2
    )
    ,
    DimDate[Year],
   "Staff>2Articles/DayAvg", DISTINCTCOUNT(all_articles_ever[UserID])
)

....which returns the desired results.  But how would I write either the above measure or TRY1 (or TRY2 if I could fix the error) to get it to return these grand totals through a measure in Reports?

Thank you for help.  I've been playing around with this for a week without success. 

[ImkeF · Accepted solution]

Hi @charleshale 

it looks as if you've misunderstood the article. You are adding a column ("ABCD") to a virtual table with SUMMARIZE and you can do the same with ADDCOLUMNS instead. So your orignal measure:

TRY1#Staff>2DailyArticles = COUNTROWS(
    FILTER(
        SUMMARIZE(
           VALUES(all_articles_ever[UserID]),
           all_articles_ever[User_ID],
           "ABCD",CALCULATE (____//measure_for_daily_articles_count//__________)
            )
      ,[ABCD]>=2)
)

will become this:

TRY1#Staff>2DailyArticles = COUNTROWS(
    FILTER(
        ADDCOLUMNS(
            SUMMARIZE( 
                VALUES(all_articles_ever[UserID]),
                all_articles_ever[User_ID]
             ),
           "ABCD",CALCULATE (____//measure_for_daily_articles_count//__________)
         )
      ,[ABCD]>=2)
)

[ImkeF]

Hi

to avoid the performance-hit of SUMMARIZE, use ADDCOLUMN for adding the columns (and SUMMARIZE just for the grouping): https://www.sqlbi.com/blog/marco/2012/09/04/optimize-summarize-with-addcolumns-in-dax-ssas-tabular-d... 

[charleshale]

Thanks, @IM (what a master you are).  Only issue here is that I am not adding columns.  So maybe summarize is as fast as I can get and it's just a massive table?

[ImkeF · Accepted solution]

Hi @charleshale 

it looks as if you've misunderstood the article. You are adding a column ("ABCD") to a virtual table with SUMMARIZE and you can do the same with ADDCOLUMNS instead. So your orignal measure:

TRY1#Staff>2DailyArticles = COUNTROWS(
    FILTER(
        SUMMARIZE(
           VALUES(all_articles_ever[UserID]),
           all_articles_ever[User_ID],
           "ABCD",CALCULATE (____//measure_for_daily_articles_count//__________)
            )
      ,[ABCD]>=2)
)

will become this:

TRY1#Staff>2DailyArticles = COUNTROWS(
    FILTER(
        ADDCOLUMNS(
            SUMMARIZE( 
                VALUES(all_articles_ever[UserID]),
                all_articles_ever[User_ID]
             ),
           "ABCD",CALCULATE (____//measure_for_daily_articles_count//__________)
         )
      ,[ABCD]>=2)
)

[charleshale]

Ah that makes sense.   Almost all is clear now.     The measure you just edited is lightning fast.  The speed improvement vs SUMMARIZE() is really incredible.

Question about ALL() filters here.   Let's say I want to now edit the measure to return the table column total (which would be the total # of UserIDs posting over 2x/day) independent of UserID row context.  Where would you put the ALL() filters given that the countrows itself needs UserID context to count the total?       

___/EXP/_"ALL>2x/day" = countrows (                   //CAN I PUT AN ALL() UP HERE?
    FILTER(
        ADDCOLUMNS(
            SUMMARIZE(
                VALUES(all_articles_ever[UserID]),
                all_articles_ever[UserID]
            ),
        "ABCD", CALCULATE(____//measure//________, //PUTTING ALL() FILTERS HERE FAILS TO RETURN GRAND TOTAL WITH ROW CONTEXT USERID )
        ),
        [ABCD]>2)
)

Put another way, in the photo below, the goal would be to edit the measure to return the grand total (here it's 109) in the right hand column.   Can this be done through ALL() + ADDCOLUMNS() & SUMMARIZE() or should I be tackling it another way? 

Thank you for your mastery. 

[charleshale · Accepted solution]

I solved this through trial and error: moving the ALL () up to the SUMMARIZE() works.   Does heck of a number on the processor but works.   

___?_ALL_#Staff>2DailyArticles = COUNTROWS( 
    FILTER(
        ADDCOLUMNS( 
            SUMMARIZE( ALL(
                all_articles_ever),all_articles_ever[user_id]
            ),
        "ABCD", CALCULATE([__DailyLocAvg_Art], all_articles_ever[Lkup_RoleID] = 4 || all_articles_ever[Lkup_RoleID] = 5 )
        ),
        [ABCD]>2)
)