Forum Discussion
If specific values are selected, return specific result
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
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!
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
Hi az38, thanks a lot for your help, it worked!