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

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

    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'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!