Power-BI / Dax: Count emails multiple times in a function based on the same dimension (#Order)

Question

Starting Point

A table contains two columns:

• E-Mail (string, Unique)
• Number of Orders (Integer)

Example Dataset:

 | E-Mail             | #Orders |
 | —————-—————-————-  |———————  |
 | user1@example.com  | 1       |
 | user2@example.com  | 1       |
 | user3@example.com  | 4       |
 | user4@example.com  | 4       |
 | user5@example.com  | 4       |
 | user6@example.com  | 5       |
 | user7@example.com  | 2       |
 | user8@example.com  | 1       |
 | user9@example.com  | 5       |
 | user10@example.com | 4       |

Goal:

A funnel analysis is to be created: It aims to analyze how many customers (= number of unique email addresses) have

• Made at least 1 order
• Made at least 2 orders
• Made at least 3 orders

Example: Made at least 2 orders = Distinctcount (E-Mail) where #Orders >= 2

Example Chart:

How do I build the solution in DAX ?

Answer 1

You need to create a calculated table where you list the label and the result :

MinOrdersTable = 
UNION(
    ROW("MinXOrder", "Min 1 Order", "Result", CALCULATE(DISTINCTCOUNT(OrdersTable[Email]), OrdersTable[Orders] >= 1)),
    ROW("MinXOrder", "Min 2 Orders", "Result", CALCULATE(DISTINCTCOUNT(OrdersTable[Email]), OrdersTable[Orders] >= 2)),
    ROW("MinXOrder", "Min 3 Orders", "Result", CALCULATE(DISTINCTCOUNT(OrdersTable[Email]), OrdersTable[Orders] >= 3)),
    ROW("MinXOrder", "Min 4 Orders", "Result", CALCULATE(DISTINCTCOUNT(OrdersTable[Email]), OrdersTable[Orders] >= 4)),
    ROW("MinXOrder", "Min 5 Orders", "Result", CALCULATE(DISTINCTCOUNT(OrdersTable[Email]), OrdersTable[Orders] >= 5))
)