AlB's avatar
AlB
Icon for Community Champion rankCommunity Champion
7 years ago
Status:
New

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   

Data Modeling

3 Comments

  • v-qiuyu-msft's avatar
    v-qiuyu-msft
    Icon for Community Support rankCommunity Support
    7 years ago

    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's avatar
    AlB
    Icon for Community Champion rankCommunity Champion
    7 years ago

    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's avatar
    charleshale
    Icon for Continued Contributor rankContinued Contributor
    6 years ago

    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

     

Completed Ideas