Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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 |
User | Count |
---|---|
42 | |
26 | |
21 | |
16 | |
15 |
User | Count |
---|---|
54 | |
35 | |
18 | |
17 | |
15 |