Calculated column to attribute customer based on the

[Anonymous]

Hi

I'm trying to solve the following problem:

I have two tables in my data model:

Dim Customer -> pink column denotes the effect I would like to achieve

CustomerID	CustomerEmail	CustomerType	CustomerSubType
1	aaa@abc.com	wholesaler	B2B
2	aaa@abc.com	retailer	B2B

Fact Sales

SaleOrderID	CustomerID
1	2
2	2
3	1

Now, I would like to create a calculated column, that in case given customer created at least one transaction as a wholesaler, will be assigned with string B2B; for all other cases (no B2B transactions or no transactions at all) it should be given B2C

I already tried below DAX code, but obviously it's evaluated on CustomerID level, so this customer (on the Email level) will be assigned two values (B2B and B2C)

VAR CountB2BOrders = 
CALCULATE ( 
DISTINCTCOUNT (SalesOrder[SalesOrderId] )
; FILTER ( 
SalesOrder; SalesOrder[CustomerID] = EARLIER ( Customer[CustomerID] ) 
)
; Customer[CustomerType] = "wholesaler" 
)

 RETURN IF ( CountB2BOrders = 0; "B2C"; "B2B" )

What I need is a unified value for CustomerSubType attribute per customer email - either B2B or B2C (as presented in the first table)

Currently, using presented DAX formula, I'm getting multiplicated entry on the Power BI report visual for the same customer email, like:

CustomerEmail	CustomerSubType
aaa@abc.com	B2B
aaa@abc.com	B2C

What I want to get is:

CustomerEmail	CustomerSubType
aaa@abc.com	B2B

[lbendlin]

CustomerSubType =

var e=Customer[CustomerEmail]

var o=CALCULATE(count(Sales[SaleOrderID]),all(sales),Customer[CustomerEmail]=e,Customer[CustomerType]="wholesaler")

return switch (o,0,"B2C","B2B")