Calculation group "Multiple or Empty Selection Expression" not behaving as expected in Excel pivot

[MBZA]

I originally picked this up with Analyse in Excel on the service, but have confirmed it happens with Desktop by using Analyse in Excel from within DAX studio.

I have tried to add a Multiple or Empty Selection Expression to a calculation group that is supposed to behave differently depending on how many calculation group items are selected. The calculation group is designed to only affect some measures, so the individual items are of the form

calculation item 1 = IF(CONTAINSSTRING(SELECTEDMEASURENAME(), "exclude"), CALCULATE(SELECTEDMEASURE(), ... some logic), BLANK())

And there is a catch-all item for the remaining measures:

Not applicable = IF(NOT CONTAINSSTRING(SELECTEDMEASURENAME(), "exclude"), SELECTEDMEASURE(), BLANK())

Now, I wanted to have a good way to combine the two sets of measures in one visual. Because the measures are mutually exclusive, so long as I select just "not applicable" and exactly one of the other items it's still sensible to have a matrix/pivot table giving the correct results for each measure and using the calculation group calculations without needing to have the calculation group on one of the axes.

My Multiple or Empty Selection Expression is set using Tabular Editor and is as follows:

SWITCH (
    TRUE(),
    COUNT ( 'My calculation group'[calc group] ) > 2, BLANK(),
    NOT CONTAINSSTRING ( SELECTEDMEASURENAME (), "exclude" ), SELECTEDMEASURE (),
    "calculation item 1" IN VALUES ( 'My calculation group'[calc group] ),
        CALCULATE (SELECTEDMEASURE(), ... some logic),
    "calculation item 2" IN VALUES ( 'My calculation group'[calc group] ),
        CALCULATE (SELECTEDMEASURE(), ... different logic),
    "calculation item 3" IN VALUES ( 'My calculation group'[calc group] ),
        CALCULATE (SELECTEDMEASURE(), ... yet more logic),
    BLANK()
)

It does what it's supposed to. I can build a matrix or pivot without including the calculation group on the axes and in both Power BI and in Analyse in Excel it gives the correct values so long as just the two items are selected. I can also build a matrix in Power BI with the calculation group on say columns and then I can select more than two calculation group items and it will give correct results for all of them.

Where I'm getting weird behaviour is if I add the calculation group to columns in an Analyse in Excel pivot. In this case the expression shouldn't have any effect because there's only one selection for each column. However, if I try to put more than two of the calculation group's items on columns I am unexpectedly getting an empty table as a result. If I remove the selection expression it behaves correctly again.

In other words for two items selected I get this:

I can also remove the calculation group from columns and I get the same results for each measure because of the selection expression:

Without the multiple selection expression the above table would be wrong because the default is just to calculate each cell with SELECTEDMEASURE().

For three items selected I want this:

But I'm getting an empty table back instead (in Excel, at least). If I remove the multiple selection expression I get the expected result.

Is there a workaround for this?

[Anonymous]

Hi,Sahir_Maharaj ,thanks for your concern about this issue.

Your answer is excellent!
Hello,@MBZA .

It's great to see that you found your problem,
you could mark the options as a solution which will help other users in the forum.

I hope my suggestions give you good ideas, if you have any more questions, please clarify in a follow-up reply.
Best Regards,
Carson Jian

[Sahir_Maharaj]

Hello @MBZA,

Instead of completely blanking out the table when more than two items are selected, can you please try this approach to modify your logic to handle Excel Pivot behavior better.

SWITCH (
    TRUE(),
    COUNT ( 'My calculation group'[calc group] ) > 2, SELECTEDMEASURE(),
    
    NOT CONTAINSSTRING ( SELECTEDMEASURENAME (), "exclude" ), SELECTEDMEASURE (),

    "calculation item 1" IN VALUES ( 'My calculation group'[calc group] ),
        CALCULATE (SELECTEDMEASURE(), ... some logic),
    
    "calculation item 2" IN VALUES ( 'My calculation group'[calc group] ),
        CALCULATE (SELECTEDMEASURE(), ... different logic),
    
    "calculation item 3" IN VALUES ( 'My calculation group'[calc group] ),
        CALCULATE (SELECTEDMEASURE(), ... yet more logic),

    SELECTEDMEASURE()
)

[MBZA · Accepted solution]

Hi Sahir,

Thanks for the response. It looks like this was a silly mistake on my part - I should have swapped the first two lines after the TRUE in the switch.