Forum Discussion

MBZA's avatar
MBZA
Helper I
1 year ago
Solved

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

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...
Need Help
  • MBZA's avatar
    MBZA
    1 year ago

    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.

Sahir_Maharaj's avatar
Sahir_Maharaj
Super User
1 year ago

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's avatar
    MBZA
    Helper I
    1 year ago

    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.

Related content