Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Add support for SUMMARIZECOLUMNS in a modified filter context

Hi all,

Currently SUMMARIZECOLUMNS is not supported fully in a  modified filter context. I find it a real pity as this is the only function that creates a table dynamically with only filter context and no row context (vs SUMMARIZE, ADDCOLUMNS, SELECTCOLUMNS).
For instance, if we create a measure including SUMMARIZECOLUMNS and we use that measure under the effect of slicers, the engine throws the error:
'SummarizeColumns() and AddMissingItems() may not be used in this context.'

When will this be supported? It would certainly be a great addition.

Best   

Status: New
Comments
v-qiuyu-msft
Community Support

Hi @AlB,

 

Would you please share a pbix file use some dummy data so we can understand the scenario well? 

 

Best Regards,
Qiuyun Yu 

AlB
Super User
Super User

Hi @v-qiuyu-msft

 

Thanks for your reply.  

If you create a measure that uses SUMMARIZECOLUMNS and the measure is affected by slicers, the engine will throw the following error:

'SUMMARIZECOLUMNS and AddMissingItems may not be used in this context'

 

In the example provided in this file, [Measure1] works fine when only a slicer on Category is applied. If we add a slicer on ID, we get the error mentioned above.

 

Thanks

 

 

 

 

charleshale
Responsive Resident

I have a data file with this issue now.  It's a monster with a bunch of sensitive data.   Any suggestions for anonymizing the data or easily slimming it down?     

 

In the meantime, maybe the following will help.   I was using the following measure just fine:

 

____#Posters_AllStaff_"ALL>2x/day" = COUNTROWS(
    FILTER(
        SUMMARIZE(
           VALUES(all_articles_ever[user_id]),
all_articles_ever[user_id],
"ABCD",CALCULATE([__DailyLocAvg_Art],all_articles_ever[Lkup_RoleID] = 4 || all_articles_ever[Lkup_RoleID] = 5)
            )
      ,[ABCD]>2)
)

 

 

Having read that SummarizeColumns is more efficient, I rewrote it in the following:

 

____#Posters_AllStaff_"ALL>2x/day"SUMCOL = countrows (
    filter(
    SUMMARIZECOLUMNS(
        all_articles_ever[user_id],
        "ABCD", CALCULATE([__DailyLocAvg_Art], all_articles_ever[Lkup_RoleID] = 4 || all_articles_ever[Lkup_RoleID])
        ),
    [ABCD]>2)
)

 

I also made tables to QA my work.  While the tables were the same, the 2nd measure throws the error "SummarizeColumns() and AddMissingItems() may not be used in this context.'" when I use it in visuals.

So, I think I will stick with my tried and true SUMMARIZE () but I'm not seeing why summarize columns shouldnt be able to deal with other filters