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 if all Actual instances of a customer are Indoor --> customer type is In...
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.

     

     

parry2k's avatar
parry2k
Icon for Super User rankSuper User
6 years ago

Anonymous customer A suppose to be Both, isnt it?

 

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

  • 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
      Icon for Community Champion rankCommunity 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