Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

If specific values are selected, return specific result

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,...
Need Help
  • az38's avatar
    az38
    6 years ago

    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

az38's avatar
az38
Community Champion
6 years ago

Anonymous 

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

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Hi az38, below is the dummy data:

 

I have a lookup table to store customer information:

Customer_lookup:

Customer NameGroup
AGroup 1
BGroup 1
CGroup 1
DGroup 2
EGroup 2
FGroup 3

Then the Sale table

Sale_data

CustomerSale
A40
A50
B10
C60
A30
B40
D20
D70
E40
F80

 

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's avatar
    az38
    Community Champion
    6 years ago

    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's avatar
      Anonymous
      Not applicable
      6 years ago

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