cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
kamilzet__
Frequent Visitor

Calculated column to attribute customer based on the

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

 

CustomerIDCustomerEmailCustomerTypeCustomerSubType
1aaa@abc.comwholesalerB2B
2aaa@abc.comretailerB2B

 

Fact Sales

 

SaleOrderIDCustomerID
12
22
31

 

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:

 

CustomerEmailCustomerSubType
aaa@abc.comB2B
aaa@abc.comB2C
 

What I want to get is:

 

CustomerEmailCustomerSubType
aaa@abc.comB2B
 

 

 

 

1 REPLY 1
lbendlin
Super User III
Super User III

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")

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

April Update

Check it Out!

Click here to read more about the April 2021 Updates!

MBAS Carousel

Sign up for our May 4th event!

May the fourth be with you, join us online!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

Top Solution Authors