Customer classification logic

[Anonymous]

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"

Customer	Status	Product type	Customer Type (desired column)
A	Test	Indoor	Outdoor
A	Actual1	Outdoor	Outdoor
A	Actual2	Outdoor	Outdoor
B	Actual1	Indoor	Indoor
B	Test	Indoor	Indoor
B	Actual2	Indoor	Indoor
C	Actual1	Outdoor	Both
C	Actual2		Both
C	Actual 3	Indoor	Both

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

[parry2k · Accepted solution]

@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]

@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]

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

[camargos88]

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