Switch measures and allow multiselection

[joanna-g2]

Hi Community,

I have a slicer based on disconnected table that includes values A, B, C, D. 
I have a table with transactional data that stores values A, B, C and D in separate columns. 

Requirement is to create an new measure, let's call it F, that is going to return A, B, C, D OR sum of any combination of those columns. 

I have created F as

F =

(VAR UserSelection =

SELECTEDVALUE(DisconnectedTable[DisconnectedTable],"G")

RETURN

SWITCH(

TRUE(),

UserSelection="A", SUM(A),

UserSelection="B", SUM(B),

UserSelection="C", SUM(C),

UserSelection="D", SUM(D),

UserSelection="G", [G],

[G]))

where

G = SUM(A) +SUM(B) + SUM(C) +SUM(D)

The problem I'm facing is that if user selects multiple answers in the slicer, selectedvalue won't be evalutated propery and G will be returned. In case multiple answers are selected, I would like the measure to sum only the selected columns. 

Example 
A = 2
B = 0
C = 4
D = 8

If I select A, I'm getting 2 which is correct.
If I select A, B and C instead of getting 6, I'm getting 14. 

Is there any other DAX formula that could be used to replace selectedvalue and allow multiselection?

[AlexisOlson · Accepted solution]

The best approach would probably be to unpivot the columns A, B, C, and D so that you avoid writing this sort of measure entirely.

If they have to be separate columns, try this:

F =
VAR SelectedValues = VALUES ( DisconnectedTable[DisconnectedTable] )
RETURN
    IF ( "A" IN SelectedValues, SUM ( [A] ) )
  + IF ( "B" IN SelectedValues, SUM ( [B] ) )
  + IF ( "C" IN SelectedValues, SUM ( [C] ) )
  + IF ( "D" IN SelectedValues, SUM ( [D] ) )

[joanna-g2]

Thanks @AlexisOlson ! Formula you provided works great! 

[Greg_Deckler]

@joanna-g2 Probably want something like:

F =
  VAR UserSelections = DisconnectedTable[DisconnectedTable]
  VAR __SumA = IF("A" IN UserSelections),SUM(A),BLANK())
  VAR __SumB = IF("B" IN UserSelections),SUM(B) + __SumA,__SumA)
  VAR __SumC = IF("C" IN UserSelections),SUM(C) + __SumB,__SumB)
  VAR __SumD = IF("D" IN UserSelections),SUM(D) + __SumC,__SumC)
RETURN
  __SumD

[joanna-g2]

Hey @Greg_Deckler !

Thanks for your input! I think it is a very good approach. 

There is one challange with the sollution you have suggested. 
VAR UserSelections requires a single value for column in the DisconnectedTable. Do you have any suggestions how to work it around? I'm not able to use the Max/Min aggregation as it will pick up only one value from the slicer.