Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi,
I have problem with measure, please help me Masters! 🙂
Two tables connected one to many.
Customers:
CustomerID | Stan | Type |
1 | LEAD | Active |
2 | Sleep | No |
3 | LEAD | Active |
Process:
ProcessID | CustomerID | Type |
1 | 1 | Lost |
2 | 2 | Progress |
3 | 3 | Main |
4 | 3 | Main |
I need to have number of CustomerID where:
[Customers].Stan = LEAD and
[Customers].Type= Active and
(customer not exist in the process table OR [Process] = Main OR [Process] = Lost)
Thank You a lot!
Solved! Go to Solution.
[# Cust] =
SUMX(
Customers,
var TheStan = Customers[Stan]
var TheType = Customers[Type]
var Result =
CALCULATE(
var DoesntExistInProcess =
ISEMPTY( Process )
var HasMainOrLostProcess =
1 <= COUNTROWS(
INTERSECT(
{"lost", "main"},
DISTINCT( Process[Type] )
)
)
var HasStanLeadAndTypeActive =
and(
TheStan = "lead",
TheType = "active"
)
var Condition =
HasStanLeadAndTypeActive
&&
(
DoesntExistInProcess
||
HasMainOrLostProcess
)
RETURN
Condition
)
return
DIVIDE( Result, Result )
)
[# Cust] =
SUMX(
Customers,
var TheStan = Customers[Stan]
var TheType = Customers[Type]
var Result =
CALCULATE(
var DoesntExistInProcess =
ISEMPTY( Process )
var HasMainOrLostProcess =
1 <= COUNTROWS(
INTERSECT(
{"lost", "main"},
DISTINCT( Process[Type] )
)
)
var HasStanLeadAndTypeActive =
and(
TheStan = "lead",
TheType = "active"
)
var Condition =
HasStanLeadAndTypeActive
&&
(
DoesntExistInProcess
||
HasMainOrLostProcess
)
RETURN
Condition
)
return
DIVIDE( Result, Result )
)
Thank you a lot!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
49 | |
26 | |
21 | |
16 | |
12 |
User | Count |
---|---|
57 | |
49 | |
44 | |
19 | |
18 |