Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Customer classification logic

Hi all, I'm trying to create logic for a new column Customer Type:

 

Customer Type classification -> Indoor, Outdoor, Both

  1. if all Actual instances of a customer are Indoor --> customer type is Indoor
  2. if all Actual instances are Outdoor --> customer type is Outdoor
  3. if some Actual instances are Indoor and some are Outdoor or null  --> Both

Please note that I want to ignore all customers with Status that does contain the text "Actual"

CustomerStatusProduct typeCustomer Type (desired column)
ATestIndoorOutdoor
AActual1OutdoorOutdoor
AActual2OutdoorOutdoor
BActual1IndoorIndoor
BTestIndoorIndoor
BActual2IndoorIndoor
CActual1OutdoorBoth
CActual2 Both
CActual 3IndoorBoth

Could someone help me figure out how to implement this logic in a new column? Thanks!

Need Help
  • parry2k's avatar
    parry2k
    6 years ago

    Anonymous try something like this, add this as a column

     

    Ask anything Power BI. Book appointment for a free consultancy at https://www.perytus.com

     

    Customer Type = 
VAR __indoor = 
COUNTROWS( 
    CALCULATETABLE( 
        VALUES ( Customer[Product type] ), 
        ALLEXCEPT ( Customer, Customer[Customer] ), 
        Customer[Product type] = "Indoor" , 
        CONTAINSSTRING( Customer[Status] , "Actual" )
    ) 
)
VAR __outdoor =COUNTROWS( 
    CALCULATETABLE( 
        VALUES ( Customer[Product type] ), 
        ALLEXCEPT ( Customer, Customer[Customer] ), 
        Customer[Product type] = "Outdoor" , 
        CONTAINSSTRING( Customer[Status] , "Actual" )
    ) 
)
RETURN
SWITCH ( TRUE(),
    __indoor = 1 && __outdoor = 1, "Both",
    __indoor = 1, "Indoor",
    __outdoor = 1, "Outdoor"
)

    Would appreciate Kudos 🙂 if my solution helped.

     

     

4 Replies

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      parry2k no, I want to ignore where status = Test, I only want to look at where Status contains the text "Actual"

      • camargos88's avatar
        camargos88
        Community Champion
        6 years ago

        Hi Anonymous ,

         

        Try this code:

         

        Column =
        VAR _result = CALCULATE(DISTINCTCOUNT('Table'[Product type]); FILTER(ALLEXCEPT('Table';'Table'[Customer]); SEARCH("Actual"; 'Table'[Status]; 1;0) > 0))
        RETURN IF(_result = 1; CALCULATE(DISTINCT('Table'[Product type]); FILTER(ALLEXCEPT('Table';'Table'[Customer]); SEARCH("Actual"; 'Table'[Status]; 1;0) > 0)); "Both")
         
        Ricardo
  • parry2k's avatar
    parry2k
    Super User
    6 years ago

    Anonymous try something like this, add this as a column

     

    Ask anything Power BI. Book appointment for a free consultancy at https://www.perytus.com

     

    Customer Type = 
VAR __indoor = 
COUNTROWS( 
    CALCULATETABLE( 
        VALUES ( Customer[Product type] ), 
        ALLEXCEPT ( Customer, Customer[Customer] ), 
        Customer[Product type] = "Indoor" , 
        CONTAINSSTRING( Customer[Status] , "Actual" )
    ) 
)
VAR __outdoor =COUNTROWS( 
    CALCULATETABLE( 
        VALUES ( Customer[Product type] ), 
        ALLEXCEPT ( Customer, Customer[Customer] ), 
        Customer[Product type] = "Outdoor" , 
        CONTAINSSTRING( Customer[Status] , "Actual" )
    ) 
)
RETURN
SWITCH ( TRUE(),
    __indoor = 1 && __outdoor = 1, "Both",
    __indoor = 1, "Indoor",
    __outdoor = 1, "Outdoor"
)

    Would appreciate Kudos 🙂 if my solution helped.