If specific values are selected, return specific result

[Anonymous]

Hi all, 

I have a column "Customer", which contains customer name from A to F.

What I need to do is if customer A, B, and C are selected by slicer, return X; if customer C and E are selected, return Y; otherwise return Z.

Your help would be really appreciated.

Thank you!

[az38]

Hi @Anonymous 

Im sure it's not the best idea, but should work

Measure = 
SWITCH(TRUE(),
COUNTROWS(FILTER('Table','Table'[Customer]="A" || 'Table'[Customer]="B" || 'Table'[Customer]="C"))=3 && COUNTROWS('Table')=3,"X",
COUNTROWS(FILTER('Table','Table'[Customer]="C" || 'Table'[Customer]="E"))=2 && COUNTROWS('Table')=2,"Y",
"Z"
)

[Anonymous]

Hi @az38 , thank you for your quick reply.

I'm sorry I didn't clearly explain the situation. Your solution works well if A-F are customers with unique rows in the table. While in my case, I have a Customer_Lookup table and a Sale table. in Sale, each customer has multiple entries.

I'm trying to slice using the column in Customer_Lookup table. Can you please help me with another solution? 

[az38]

@Anonymous 

Hi, show please full detail of your data model (with dummy data) and desired output 🙂

[Anonymous]

Hi @az38, below is the dummy data:

I have a lookup table to store customer information:

Customer_lookup:

Customer Name	Group
A	Group 1
B	Group 1
C	Group 1
D	Group 2
E	Group 2
F	Group 3

Then the Sale table

Sale_data

Customer	Sale
A	40
A	50
B	10
C	60
A	30
B	40
D	20
D	70
E	40
F	80

I've created a slicer using 'Customer_lookup'[Customer Name]. What I'm trying to do is only show SUM(Sale_data[Sale]) only when all members in the group is selected.

For example, when I select A, B, and C, SUM is calculated (result: 230). When different combinations that does not form any group are selected, no data is shown.

It may seem better if I create slicer based on group, but for some reason it's better for my users to slice based on individual selection like this.

Many thanks for your assistance!

[az38 · Accepted solution]

@Anonymous 

thx for great explanation

i have rewritten my measure

Measure3 = 
SWITCH(TRUE(),
COUNTROWS(FILTER('Customer_lookup','Customer_lookup'[Customer Name]="A" || 'Customer_lookup'[Customer Name]="B" || 'Customer_lookup'[Customer Name]="C"))=3 && COUNTROWS('Customer_lookup')=3,CALCULATE(SUM(Sale_data[Sale])),
COUNTROWS(FILTER('Customer_lookup','Customer_lookup'[Customer Name]="C" || 'Customer_lookup'[Customer Name]="E"))=2 && COUNTROWS('Customer_lookup')=2,CALCULATE(SUM(Sale_data[Sale])),
BLANK()
)

but then again Im sure this is not the best and beautiest solution

and make sure you have relationships between your tables by [Customer_Name] -> [Customer] fields

[Anonymous]

Hi @az38, thanks a lot for your help, it worked!