cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
charleshale Member
Member

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

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

ArticleIDAuthorIDRoleIDDate
00001_I_love_powerBI121412/13/2019
00002_I_learning_powerBI_is_fun121412/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?

image.png

 

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

2 ACCEPTED SOLUTIONS

Accepted Solutions
Super User
Super User

Re: Trying to replace Summarize() with SummarizeColumns - AddMissingItems() Error + advanced ALL() i

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

 

 

If you liked my solution, please give it a thumbs up. An if I did answer your question, please mark my post as a solution. Thanks!

Proud to be a Datanaut!

Imke Feldmann

How to integrate M-code into your solution -- Check out more PBI- learning resources here -- Performance Tipps for M-queries




View solution in original post

charleshale Member
Member

Re: Trying to replace Summarize() with SummarizeColumns - AddMissingItems() Error + advanced ALL() i

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

View solution in original post

5 REPLIES 5
Super User
Super User

Re: Trying to replace Summarize() with SummarizeColumns - AddMissingItems() Error + advanced ALL() i

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... 

If you liked my solution, please give it a thumbs up. An if I did answer your question, please mark my post as a solution. Thanks!

Proud to be a Datanaut!

Imke Feldmann

How to integrate M-code into your solution -- Check out more PBI- learning resources here -- Performance Tipps for M-queries




charleshale Member
Member

Re: Trying to replace Summarize() with SummarizeColumns - AddMissingItems() Error + advanced ALL() i

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?

Super User
Super User

Re: Trying to replace Summarize() with SummarizeColumns - AddMissingItems() Error + advanced ALL() i

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

 

 

If you liked my solution, please give it a thumbs up. An if I did answer your question, please mark my post as a solution. Thanks!

Proud to be a Datanaut!

Imke Feldmann

How to integrate M-code into your solution -- Check out more PBI- learning resources here -- Performance Tipps for M-queries




View solution in original post

charleshale Member
Member

Re: Trying to replace Summarize() with SummarizeColumns - AddMissingItems() Error + advanced ALL() i

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. 

 

image.png

 
charleshale Member
Member

Re: Trying to replace Summarize() with SummarizeColumns - AddMissingItems() Error + advanced ALL() i

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

View solution in original post

Helpful resources

Announcements
New Topics Started Badges Coming

New Topics Started Badges Coming

We're releasing new versions of the badge that everyone's talking about. ;) Check your inbox for notifications.

MBAS 2020

Save the new date (and location)!

Our business applications community is growing—so we needed a different venue, resulting in a new date and location. See you there!

Difinity Conference

Difinity Conference

The largest Power BI, Power Platform, and Data conference in New Zealand

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

Top Solution Authors
Top Kudoed Authors (Last 30 Days)