topic Re: Add row/filter context to SUMMARIZECOLUMNS measure in DAX Commands and Tips

Add row/filter context to SUMMARIZECOLUMNS measure

RobinNeven — Wed, 08 Jun 2022 15:10:33 GMT

Hi,

I have the following measure, which works on a total level, but not in a table visual with fields from another table (dimCampaigns):

Unique Opened = CALCULATE ( COUNTROWS ( SUMMARIZECOLUMNS( fctActivities[CONTACT_ID], fctActivities[CAMPAIGNID], fctActivities[SUBJECTLINE], "CountEmailOpen", CALCULATE ( COUNTROWS ( fctActivities ), fctActivities[ACTIVITYTYPE] = "EmailOpen" ) ) ) )

This is probably because SUMMARIZECOLUMNS doesn't have a row context (as stated by this article). So I figured I would have to somehow retrieve such a value from the filter context of the table visual containing data from 'dimCampaigns' into the measure manually (by using VALUES). So I created the following:

This still works on the total level, but still not in the table visual. It's the following table visual where I would like to add the measure to, but if I use the above measure it gives the dreaded error "SummarizeColumns() and AddMissingItems() may not be used in this context.":

I think I'm close, but I just cannot get it to work. Any help would be much appreciated, thank you!

Re: Add row/filter context to SUMMARIZECOLUMNS measure

johnt75 — Wed, 08 Jun 2022 16:19:43 GMT

I don't think you'll get SUMMARIZECOLUMNS working in a measure, try using ADDCOLUMNS .. SUMMARIZE instead, e.g.

Unique Opened = CALCULATE ( COUNTROWS ( ADDCOLUMNS ( SUMMARIZE ( fctActivities, fctActivities[CONTACT_ID], fctActivities[CAMPAIGNID], fctActivities[SUBJECTLINE] ), "CountEmailOpen", CALCULATE ( COUNTROWS ( fctActivities ), fctActivities[ACTIVITYTYPE] = "EmailOpen" ) ) ) )

Re: Add row/filter context to SUMMARIZECOLUMNS measure

RobinNeven — Mon, 13 Jun 2022 09:44:36 GMT

Hi johnt75 ,

Thanks for your reply! It's not an ideal solution, but what I did now is the following:

Create a calculated table using the following DAX code (as I think it's better to use SUMMARIZECOLUMNS than SUMMARIZE/ADDCOLUMNS, as stated in the article I shared earlier), which is connected to the DimCampaigns table:

Subset = SUMMARIZECOLUMNS( fctActivities[CONTACT_ID], fctActivities[CAMPAIGNID], "CountEmailOpen", CALCULATE ( COUNTROWS ( fctActivities ), fctActivities[ACTIVITYTYPE] = "EmailOpen" ) )

Create a measue in which I simply COUNTROWS in the above calculated table named 'Subset':

Unique Opened = COUNTROWS('Subset')

Somehow it doesn't work to simply replace 'Subset' in the measure with the DAX code that's creating the calculated table, also not using variables, this probably has something to do with context transition not working.

Re: Add row/filter context to SUMMARIZECOLUMNS measure

johnt75 — Mon, 13 Jun 2022 09:45:45 GMT

Replacing the table name with the DAX, or a variable calculated from the DAX, won't work if you use SUMMARIZECOLUMNS, you can't use that in a measure. If you edit it to use ADDCOLUMNS ... SUMMARIZE then it might work

Re: Add row/filter context to SUMMARIZECOLUMNS measure

RobinNeven — Mon, 13 Jun 2022 09:52:09 GMT

So you mean creating one single measure as the following? Because that doesn't work either...

Unique Opened = CALCULATE ( COUNTROWS ( ADDCOLUMNS ( SUMMARIZE ( fctActivities, fctActivities[CONTACT_ID], fctActivities[CAMPAIGNID] ), "CountEmailOpen", CALCULATE ( COUNTROWS ( fctActivities ), fctActivities[ACTIVITYTYPE] = "EmailOpen" ) ) ) )

Re: Add row/filter context to SUMMARIZECOLUMNS measure

johnt75 — Mon, 13 Jun 2022 09:59:12 GMT

Try creating a temporary table to see if you can spot what the problem might be,

Tmp Table = ADDCOLUMNS ( SUMMARIZE ( fctActivities, fctActivities[CONTACT_ID], fctActivities[CAMPAIGNID] ), "CountEmailOpen", CALCULATE ( COUNTROWS ( fctActivities ), fctActivities[ACTIVITYTYPE] = "EmailOpen" ) )

Re: Add row/filter context to SUMMARIZECOLUMNS measure

RobinNeven — Mon, 13 Jun 2022 10:53:05 GMT

There is no problem with that code as such. The problem lies in the combination of that piece of code in a measure like below, the problem being that the measure is not properly filtered by (fields from) dimCampaigns in said table visual.