Add row/filter context to SUMMARIZECOLUMNS measure

[RobinNeven]

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:

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

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!

[johnt75]

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

[RobinNeven]

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.

[johnt75]

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

[RobinNeven]

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

[johnt75]

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

[RobinNeven]

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.

Unique Opened = 
CALCULATE (
    COUNTROWS (
        ADDCOLUMNS (
            SUMMARIZE (
                fctActivities,
                fctActivities[CONTACT_ID],
                fctActivities[CAMPAIGNID]
            ),
            "CountEmailOpen",
                CALCULATE (
                    COUNTROWS ( fctActivities ),
                    fctActivities[ACTIVITYTYPE] = "EmailOpen"
                )
        )
    ),FILTER('Subset', 'Subset'[CountEmailOpen] > 0)
)