switch measure with multiple select slicer data

[Anonymous]

Hi, I have a slicer "D_1_Deck" with four items as below: AMS APJ EMEA Renewal The four items are sales territories but for different product types: AMS, APJ and EMEA are for New products, Renewal is for Renew products. I am using Selectedvalue function to take the user's choice: Selected Deck = SELECTEDVALUE(D_1_Deck[Deck]). This Selected Deck was used by the following formula: Sales Amount = SWITCH(true(), [Selected Deck] in {"AMS","APJ","EMEA"}, [Total Sales Amount New Product], [Selected Deck]="Renewal",[Total Sales Amount Renew Product], BLANK()) Due to the restriction of Selectedvalue function. the user now can only make one choice out of the four slicer items. I want to allow the user to make multiple choices (any combination of the four items) so that the Sales Amount will calculate the sum of all the choices the user make. Thanks for help. PBISean

[Anonymous]

Hi, I have a slicer "D_1_Deck" with four items as below: AMS APJ EMEA Renewal The four items are sales territories but for different product types: AMS, APJ and EMEA are for New products, Renewal is for Renew products. I am using Selectedvalue function to take the user's choice: Selected Deck = SELECTEDVALUE(D_1_Deck[Deck]). This Selected Deck was used by the following formula: Sales Amount = SWITCH(true(), [Selected Deck] in {"AMS","APJ","EMEA"}, [Total Sales Amount New Product], [Selected Deck]="Renewal",[Total Sales Amount Renew Product], BLANK()) Due to the restriction of Selectedvalue function. the user now can only make one choice out of the four slicer items. I want to allow the user to make multiple choices (any combination of the four items) so that the Sales Amount will calculate the sum of all the choices the user make. Thanks for help. PBISean

[AlB]

Hi @Anonymous

What do you want to do exactly? Can you explain it a bit more? For instance, what would happen if the user selects "AMS", "APJ"  and "Renewal"?

[Anonymous]

Hi AIB,

Thanks for replying. 

Yes, I want to allow users to multi-select from the slicer, and then my formula of total amount of sales will calculate the sum of all the sales for the items the user selected. 

For example, user should be able to select AMS and APJ and EMEA, the slicer will pass the 3 item selection to the Total Amout of Sales formula and the formula will calculate the sum of total sales of AMS, APJ and EMEA. 

Currently, the Selectedvalue canl pass only ONE choice to the Total AMout of Sales formula. 

Thanks again for help.

PBISean

[AlB]

@Anonymous

Ok but what if the choice includes "Renewal"? You seem to be using a different measure in that case.

Can you show the codes of your two measures [Total Sales Amount New Product] and [Total Sales Amount Renew Product]?

Can you show the structure of your tables as well?

[Anonymous]

Yes, you are absolutely right. Ideally, the multiple choice should also allow "Renewal" which is using a different measure/formula than the "AMS", "APJ" & "EMEA". 

But for this moment, I would be happy if we can allow multiple choices without "Renewal". 

The measures for Total Sales Amount New Product & Total Sales Amount Renew Products are as follows. The only difference is the filter on the Revenue Type. 

Total Sales Amount New Product=

CALCULATE(SUM(Sales Table[Sales Amount]), Sales Table filter 1,,,,Sales Table[Revenue Type]="New"),

Total Sales Amount Renew Product=

CALCULATE(SUM(Sales Table[Sales Amount]), Sales Table filter 1,,,,Sales Table[Revenue Type]="Renew")

Thanks,

PBISean

[v-juanli-msft · Accepted solution]

Hi @Anonymous

How about this measure:

Sales Amount = 
SWITCH (
    TRUE (),
    MAX(Sheet3[cate]) IN { "AMS", "APJ", "EMEA" }, [Total Sales Amount New Product],
    MAX(Sheet3[cate]) = "Renewal", [Total Sales Amount Renew Product],
    0
)

What the expected output should be when i select "Renewal" & "APJ" & "EMEA"?

Best Regards

Maggie

[Anonymous]

Maggie,

It works! This is amazing.

I never had the faintest idea you can use MAX in this situation. I thought MAX is only to find the maximum value in a column or among numbers as illustrated by:

https://docs.microsoft.com/en-us/dax/max-function-dax

How come you can use MAX in this way? I don't get it. 

But it works! 

Thanks,

PBISean

[AlB]

@Anonymous

I'm still a bit confused as to what the exact requirements are but you can test if the user has selected any of those three values in the slicer for instance with this: 

COUNTROWS (INTERSECT({"AMS", "APJ", "EMEA"}, VALUES (D_1_Deck[Deck]))) > 0

  which will be TRUE when the selection includes any of the three.

Like I said, it would help clarify everything if you show the structure of your data model/tables.